Error in SUM function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a collumn of values that I'm trying to SUM (the values are below).
The result of the SUM is 82104,100000000100000000000000000000 when it should
simply be 82104,1.
So Excel is adding and extra 0,0000000001.

Why is this?

Thanks.
Rodrigo

2932,420000000000000000000000000000
2854,440000000000000000000000000000
1617,520000000000000000000000000000
1617,520000000000000000000000000000
1141,780000000000000000000000000000
1617,520000000000000000000000000000
853,160000000000000000000000000000
1068,830000000000000000000000000000
1110,060000000000000000000000000000
2854,440000000000000000000000000000
888,050000000000000000000000000000
888,050000000000000000000000000000
1490,650000000000000000000000000000
1002,230000000000000000000000000000
599,430000000000000000000000000000
2854,440000000000000000000000000000
888,050000000000000000000000000000
853,160000000000000000000000000000
2854,440000000000000000000000000000
1934,680000000000000000000000000000
555,030000000000000000000000000000
704,100000000000000000000000000000
853,160000000000000000000000000000
738,980000000000000000000000000000
320,220000000000000000000000000000
-763,610000000000000000000000000000
-295,590000000000000000000000000000
919,760000000000000000000000000000
1617,520000000000000000000000000000
691,410000000000000000000000000000
1002,230000000000000000000000000000
704,100000000000000000000000000000
704,100000000000000000000000000000
888,050000000000000000000000000000
1033,940000000000000000000000000000
853,160000000000000000000000000000
1268,640000000000000000000000000000
631,150000000000000000000000000000
631,150000000000000000000000000000
1268,640000000000000000000000000000
1268,640000000000000000000000000000
2854,440000000000000000000000000000
1506,510000000000000000000000000000
5671,050000000000000000000000000000
1712,660000000000000000000000000000
423,440000000000000000000000000000
5444,210000000000000000000000000000
853,160000000000000000000000000000
-18,960000000000000000000000000000
28,440000000000000000000000000000
-767,840000000000000000000000000000
-94,800000000000000000000000000000
5444,210000000000000000000000000000
2414,940000000000000000000000000000
853,160000000000000000000000000000
1268,640000000000000000000000000000
704,100000000000000000000000000000
423,440000000000000000000000000000
5444,210000000000000000000000000000
423,440000000000000000000000000000
 
Excel, like nearly every other computer program, uses IEEE Double
Precision Floating Point number. Most fractional numbers cannot
be stored EXACTLY in a variable, just as you cannot express the
number 1/3 EXACTLY in decimal form. Some rounding will occur.
You are seeing the cumulative effect of rounding. See
www.cpearson.com/excel/rounding.htm for more info.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Rodrigo_Paulino" <[email protected]>
wrote in message
 
Excel's math is right, but you are seeing the impact of unavoidable
approximations to your inputs.

Moreover, you are fooling yourself by displaying up to 34 figures per
number. Excel's documented limit is 15 figures; any additional figures
requested will simply be zero, and not reflective of the actual value used.

As Chip's excellent article indicates, Excel (and almost all other software)
follows the IEEE standard for double precision binary representation of
numbers. As a result, most integers >2^15 (=9007199254740992) and most
decimal fractions can only be approximated. Chip's article gives the theory.
You can use my VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to see the actual values of the approximations used.

Since the issue is approximation to inputs, and not the subsequent math, you
can use your knowledge of your particular calculation (addition/subtraction
of numbers which have no more than 2 decimal places) to recognize that
anything beyond the 2nd decimal place is residue from those initial
approximations that can be rounded away without violence to the calculation.

The following example may help develop your intuition for why you got an
unexpected result. If you had a hypothetical computer that did 4-figure
decimal arithmetic, then
=1-2*(1/3)
would be evaluated as
=1-2*0.3333=1-0.6666=0.3334
As you can see, the math is correct, but the final answer is not the best
approximation to 1/3, because of the original approximation to the inputs.

Jerry
 
Thank you both, Chip and Jerry.

I understood the reason why there appears to be this "residue" in the math.
I was not aware of the way how Excel stores numbers.
The article was very useful.

Best wishes,
Rodrigo
 
Back
Top