Roundoff error?

[Bill Allen]

I have an expression:
=sin(radians(90))*cos(radians(90))
The result is -6.1257E-17.
How do I get this to be zero?
Thank you

[Sam Capricci]

deleted, irrelevant to the issue.

[MrShorty]

What precision to you expect from your calculations? Something like =ROUND(formula,expected precision) should work, but you need some idea as the programmer how close to 0 ought to be seen as exactly 0.

edit to add: The other question might be to understand exactly what problem you are experiencing when Excel returns x.xxE-17 type of numbers. Perhaps a more appropriate strategy could be discussed if we better understood the exact nature of the problem this presents.

[TMS]

If you format the result as Number, it will display 0.00. If you increase the decimals, it will, eventually, show 0.0000000000000000612323400. That is, 6.12323E-17 (a positive number).

[Bill Allen]

What precision to you expect from your calculations? Something like =ROUND(formula,expected precision) should work, but you need some idea as the programmer how close to 0 ought to be seen as exactly 0.

edit to add: The other question might be to understand exactly what problem you are experiencing when Excel returns x.xxE-17 type of numbers. Perhaps a more appropriate strategy could be discussed if we better understood the exact nature of the problem this presents.

It would be helpful to me to know why Excel gives this result when my simple calculator (and my own brain) quickly returns a value of zero.

[MrShorty]

How deep down the "floating point arithmetic and error" rabbit hole do you want to go? The short answer is that computers (including Excel) are limited in how many digits they can use to "approximate" numbers and calculations. Because the calculation can never be exact (unless you are using a programming language like MathCad that knows to force cos(pi/2) to be exactly 0), calculations like this frequently return a result that is a few bits off of the theoretically correct calculation. We as programmers cannot change how computers perform arithmetic, so we just have to figure out how we are going to account for floating point anomalies.

If you want a deeper dive, I suggest messaging user curiouscat408 and see if he will chime in. He seems much more experienced tracing floating point errors through the entire arithmetic sequence.

If you want to do a deep dive into floating point arithmetic, this might be a good start: https://docs.oracle.com/cd/E19957-01..._goldberg.html

[curiouscat408]

There are two possible and equally plausible explanations. I think MrShorty already covered them adequately.

The first is: Bill's calculator might store numbers internally with much more precision than it displays. Consequently, displayed calculations might round differently. That is certainly true of the Windows (Win7) Calculator application.

The second is: The calculator might make a special case of COS(RADIANS(90)) (*) and return exactly zero. Unfortunately, Excel does not do that. And as I explained in post #5 in another thread (click here), the series sum to approximate COS happens to return 6.1257422745431001E-17 (17 sig digit precision).

(* Or COS(90) in deg mode. Bill does not say exactly what he enters on the calculator.)

Aside.... Since both SIN(RADIANS(90)) and COS(RADIANS(90)) return positive values in Excel, I have no idea how =sin(radians(90))*cos(radians(90)) returns -6.1257E-17 (sic), a negative number. I presume that Bill mistyped something.