Substraction error in Excel

[leon]

If i substract 8705.94 from 9531.55 the result is 825.61
but when i compare the calculated result with a manually
entered value of 825.61 Excel says that the 2 numbers are
not equal.
Test was done on Excel 200 and Excell 2002 with same fault

 

[Mike]

Use the formula =ROUND(9531.55-8705.94,10) instead of =9531.55-8705.94

 

[Jerry W. Lewis]

Mike has suggested the workaround. Here is the reason:

Excel (the Pentium math coprocessor, and almost all other numerical
software) follow the IEEE double precision standard for binary math.
The math is exact, but the input numbers themselves are not.

None of the numbers 8705.94 9531.55 and 825.61 can be represented
exactly in binary (much as 1/3 cannot be represented exactly in
decimal). When you enter 9531.55, what you really get is the closest
possible binary approximation to 9531.55 (which will agree with your
intent to at least 15 figures). The subtraction then becomes

9531.55000000000?????
-8705.94000000000?????
---------------------
825.61000000000?????

as you can see, since the inputs were only accurate to at most 15
figures, the result of the subtraction is only accurate to at most 14
figures. In fact, if you format the answer to show 15 figures (12
decimal places) you will see that the value is 825.609999999999, which
is the correct answer given the approximate inputs.

Integer values (up to 9007199254740991) can be represented exactly in
IEEE double precision, but most decimal fractions that superficially
appear to be exact have no exact binary representation.

Jerry

 

[Gilles Desjardins]

Thank you Jerry, it is the best explanation I have seen. I knew the reason
but could not put it in words. Great stuff!

Gilles