## 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

## RE: Someone posted once had this prob

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

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

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