incorrect addition of decimals

  • Thread starter Thread starter Tom Hoban
  • Start date Start date
T

Tom Hoban

In excell 2000 If I paste =a1+.00001 in a column with 1
in a1. I get a result like "1.000420000000100". by row
43. The problem seems to occur on the 13th figure after
the decimal. Is there a way to fix this problem?
 
Tom,

I tried reproducing this error as I also have Excel 2000. I'm running it on
Win2000.
The error starting occuring at row 78 on my set-up!?

Anyway, one solution is to use the following formula in A2 onwards -

=ROUND(A1+0.00001,5)

This seemed to fix it for me.

regards,

JohnI
 
The issue is finite precision, coupled with binary math. This issue is
common to almost all computer programs on all platforms, since only very
special purpose programs do algebraic or decimal math.

For example, IF you were doing math with 4 decimal digits, then 1/3
would be approximated by 0.3333, and if you added three of these
approximate values together, you would get 0.9999 instead of 1.

It less obvious, but 0.00001 (and most other decimal fractions) cannot
be exactly represented in binary, so must be approximated. Then you add
up several such approximations ...

You could reduce the accumulation of approximations by rounding as Tom
suggested. Alternately use
=1+(ROW()-1)/100000
to only approximate the final results, not the portion that you are
accumulating.

Jerry
 
Hi Tom,

The reason this is happening is because the numbers are converted from
decimal (base 10) to binary (base 2) so that the microprocessor can
perform the arithmetic, then the result is converted back from binary
to decimal for display in Excel. Since many numbers that can be
represented exactly in decimal can only be represented approximately
in binary (and vice versa), sometimes calculation results are slightly
incorrect from our decimal perspective.

Rounding is often helpful but not always bulletproof. The only
bulletproof way to avoid the problem is to never convert the numbers
to binary in the first place, and instead perform the calculations in
decimal. Excel can't do that on it's own, but my Excel Add-In called
xlPrecision can.

You can download the free edition of xlPrecision from:

http://PrecisionCalc.com

Modifying your example, you could use this xlPrecision formula:

=xlpADD(A1,0.00001)


Since xlPrecision does not use the microprocessor to perform its
arithmetic, it's slower than Excel's calculations. But it's fast
enough that you might not notice the difference.

BTW, avoiding the conversion to binary isn't xlPrecision's main
purpose. xlPrecision also gives you much higher precision than Excel
alone, and lets you work with much larger (and much smaller) numbers
than Excel can work with alone. Details are at:

http://www.precisioncalc.com/What is xlPrecision.html


Hope this helps,

Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right
 
Back
Top