×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Someone posted once had this prob

Someone posted once had this prob

Someone posted once had this prob

(OP)

Someone posted once had this problem and I had the same issue and I have managed to solve this issue by formatting the cell as follows.

Question was:
Does anybody know why I'd get
Sin(pi()) = 1.22515 E-16 and Sin(2*pi()) = 2.4503 E-16 ?
For what it's worth, I get the same thing with Sin(radians(180)).
Sin(0), Sin(pi()/2), and Sin(3*pi()/2) all work.
I get the same type of problem (different magnitude and shifted 90°) with cosine.

Answer is:
after assigning the formula to cell in excel =SIN(RADIANS(180)) will give you this = 2.4503 E-16 ?
Right click on cell>format cells>custom>select 0.0000
this should solve the issue and now you will get =SIN(RADIANS(180)) = 0.0000
Any confusion let me know I am happy to explain it :)

RE: Someone posted once had this prob

Note that this doesn't change the actual number, it just changes how it is displayed. So if you use that cell's value in another calculation, the error will still be there. Whether that matters is up to you and your particular situation.

RE: Someone posted once had this prob

Welcome to the site.

Quote:

Does anybody know why I'd get

Excel's value for pi is 3.141592653589790000000000. The zeros indicate the truncation of pi's mathematical value due to limitations in Excel's mathematical precision, which is some form of 32-bit binary floating point. Interestingly, Excel actually gives a worse answer if its numerical representation of pi is directly inputted into its sine function.

Just for giggles, this is Mathcad's attempt using 100 digits of precision for pi


TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: Someone posted once had this prob

This is not an error. This is limited precision math.

Excel, nor anyone else, can express pi exactly. Thus Excel is actually calculating SIN(a number really close to pi), which by definition isn't zero. I love IRstuff's example from Mathcad.

Excel uses IEEE 754 double precision math, which is a 64 bit representation. There's no exact equivalence but this format can accurately represent roughly 15 or 16 significant decimal digits.

When you ask Excel to calculate SIN(PI()/2) the answer should be about 1.000000000000000061, but only the first 15 or 16 digits can be stored by Excel, so it saves 1.0 as the answer. In some sense, this is actually the erroneous result!!

Excel is correct for both SIN(PI()) and SIN(PI()/2), but can't store the answers to their full accuracy.

RE: Someone posted once had this prob

Some more comments on this:
You don't need a custom format. Format as "number" or "scientific", and set the precision to a reasonable number.

As said by Geoff13, Excel uses 64 bit floats, not 32 bit. This is the same as the standard floats used in any other calculation program, and is more than enough for almost any practical purpose.

Precision problems become important when looking at the difference between two large nearly equal numbers, or checking if two numbers are equal.

It's not just pi, any non-integer values can give unexpected results when looking at the last decimal places.

If checking if two numbers are equal, always check if the absolute value of their difference is less than an appropriate tolerance, rather than exactly zero.

For more background and examples see:
https://newtonexcelbach.com/2021/04/05/floating-po...
and
https://newtonexcelbach.com/2011/12/20/when-does-3...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Someone posted once had this prob

If you want it to show zero, just round to something less than 15 decimal places.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close