sin(x)^2+cos(x)^2 = 1 on different calcs

[J-F Garnier]

(09-26-2018 05:27 AM)cyrille de brébisson Wrote:  However, I have NO clue what might be going on with
=1/3+1-1-1/3 and =(1/3+1-1-1/3)
This is a very interesting case! and I really would like to know what is happening behind the scenes!

1/3 = 0.0101010101... (binary)
1/3+1 = 1.01010101... (shift the mantissa right by 2 bits, loosing one bit at the mantissa end)
1/3+1-1 = 0.01010101... ( one bit is 0 at mantissa end)
so the final difference 1/3+1-1-1/3 is one bit.

Quote:=100-99.99-0.01 -> 5.1157E-15
shows that it does not "move small differences to 0....

This case is more complex, because 99.99 and 0.01 doesn't have simple binary representations. The difference 5.1157E-15 has a mantissa with several bits set (more than two).

(09-26-2018 06:37 AM)Werner Wrote:  Cyrille:
Same for =1-0.99-0.01 vs. =(1-0.99-0.01)
It shows Excel does cheat, but that can never be done consistently.
They should've adopted decimal floating point a long time ago.

=(1-0.99-0.01) -> 8.67362E-18 = 2^-57+2^-59
Maybe excel is cheating when the mantissa has up to 2 bits close to the end.

And I'm happy that excel is using binary arithmetic, I never encountered such situations in real life but had to deal with very large and complex spreadsheets to do system simulations, and speed was the main point.

J-F