Someone posted once had this prob

[RanaYaqub]

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

 

[Stick.]

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.

 

[IRstuff]

Welcome to the site.

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 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Geoff13]

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.

 

[IDS]

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-point-precision-problems/

and

https://newtonexcelbach.com/2011/12/20/when-does-35-not-equal-35/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BridgeSmith]

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