Excel making adding errors

  • Thread starter Thread starter David B
  • Start date Start date
D

David B

I just encountered something odd. I had 2 numbers in my excel sheet:
"2690.33" (a typed in number) and 2690.33 (the result of a formula). I had a
cell checking to see if these two numbers were equal and the result of that
formula was FALSE! I expanded the result of the formula only to find out it
was actually 2690.33000000002. The formula giving this nearly correct answer
was adding two numbers together " -23729.25" (typed out literal number) and
26419.58 (the result of a formula). I checked the 26419.58 formula result by
expanding its precision as far as excel would go and it had no hidden
fraction (I expanded it to
26419.5800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000).
I would call this a bug but it seems ludicrous to me that a bug as huge as
this could still be in the program. Could someone take a look at this and
tell me if I am missing something? Is there any way to fix this?
 
This is just rounding error. If you need exactly two digits, then use the
=ROUND() function,
 
Excel (and almost all other software) works in binary. Most terminating
decimal fractions (including .33) are non-terminating binary fractions that
can only be approximated (just as 1/3 can only be approximated as a decimal
fraction). As a result, it is possible for calculations to accumulate
discrepancies between unavoidable approximations and the intended values to
the point where they can be observed within Excel’s documented display limit
of 15 decimal digits.

I have never seen an example where Excel’s basic arithmetic (presumably
performed in the math coprocessor, and not re-invented in Excel) was actually
in error, so in the absence of adequate information behind your formula and
its inputs, I agree with Gary’s Student that your case must be presumed to be
the result of correct arithmetic starting from approximate inputs.

For many calculations, it is possible to reduce the impact of initial
approximations and finite precision math by judicious rounding, although the
appropriate level of rounding depends on the type of calculations performed,
and hence must be determined by a thoughtful user; there is no “one size that
fits all†that Microsoft could supply. As an example, if you are doing
accounting where you are only adding and subtracting numbers that have no
more than 2 decimal places, then rounding results to 2 decimal places would
effectively eliminate the impact of initial approximations and finite
precision math with no violence to the intended calculations, yet such severe
rounding would completely invalidate some other types of calculations.

Jerry
 
Back
Top