Decimals???

  • Thread starter Thread starter lknet3
  • Start date Start date
L

lknet3

I noticed that this calcualtion will end up in decimals on excel 2000/2003:

2,736.70 + 112.96 - 2,757.38 = 92.2799999999997

It should be exactly 92.28. Any ideas?
 
You can simplify to
2849.66 - 2,757.38 = 92.2799999999997
However, as Shane noted, Excel substitutes approximate binary values. Most
terminating decimal fractions (including .7 .96, .38 and .66) are
non-terminating binary fractions that can only be approximated in finite
precision (just as 1/3 can only be approximated as a decimal fraction).

The approximate problem that Excel (and almost all other computer hardware
and software use, since it is defined by the IEEE 754 standard) is
2849.65999999999985448084771633148193359375
-2757.3800000000001091393642127513885498046875
----------------------------------------------
92.2799999999997453414835035800933837890625
If you do the math, you will see that this is the exactly correct answer to
the approximate problem.

Excel's documented display limit of 15 decimal figures makes this appear to
be much more mysterious than it really is, because the first two numbers
appear to be exactly what you thought they were instead of approximations to
those values. The simplest way to think about it is to allow that anything
beyond the 15th decimal digit may be different than you expect, thus the
calculation can be usefully thought of as
2849.66000000000??
-2757.38000000000??
-------------------
92.28000000000??
which is entirely consistent with the actual result of
92.2799999999997...

Given that you get the exact answer to an approximate problem, where each
approximation to inputs is correct to at last 15 decimal digits, it becomes
relatively straightforward to adjust for it in programs. Two common ways are
to either test for approximate instead of exact equality, or to round each
result to an appropriate number of figures (based on the particular
calculation being done).

Jerry
 
Back
Top