Excel SUM error

  • Thread starter Thread starter Greg W
  • Start date Start date
G

Greg W

I have seen this twice now on different versions of
Excel. Type in the following numbers into excel:

-2900000.00
35848.75
21548.43
1750.00
313.33
423.33
28188.61
2900.00
1450.00
2030.00
50.00
500.00
26.00
125.00
750.00
25.00
2100000.00
5700.92
200.00
648170.63
50000.00

If I do a SUM of the numbers it should equal zero,
however if you show enough decimal places the result
Excel gives is actually 0.000000000232831

Can anyone duplicate this? Is there an explanation?

Thanks,

Greg
 
This is the way excel does it. Use rounding within or on the sum to get the
desired result.
 
If you created this list as a result of a calculation in
which you used a formula such as multiplying a percentage
with out using the rounding function, You would probably
get this. Highlight the column of Numbers you are summing
and increase the decimal places and you will see why you
don't get 0.
 
Thanks, but this article seems a lot more complicated
than what I am experiencing. I would think that Excel
would be capable of accurately adding 21 numbers that
have only 2 decimal places.
 
All of the numbers are typed in exactly how they are
listed below. Try it, it is very strange.
 
Thanks, but this article seems a lot more complicated
than what I am experiencing. I would think that Excel
would be capable of accurately adding 21 numbers that
have only 2 decimal places.
Click to expand...
...

Fine. You're wrong. If you don't want to try to understand the article, it'll
continue to be a mystery for you.

If you find this motivational, perhaps you could try designing a computer that
does all its arithmetic in decimal but is also constrained to a finite number of
decimal places (let's say 15), then figure out how you'd handle

1/3 + 1/3 + 1/3

vs

0.3333333333333330 + 0.3333333333333330 + 0.3333333333333330

In both, all operands have exactly the same 15-place decimal representation,
0.333333333333333, but the former should sum to 1.0 while the latter should sum
to 0.9999999999999990. How would you handle that?

You could always use the precision as displayed option. Read about it in online
help. It'll solve addition/subtraction problems, but at the cost of introducing
different multiplication/division/exponentiation problems.
 
They are. Haven't you seen the results.
Click to expand...

Nah. The Nasa folks are real engineers. They can make their own mistakes in
FORTRAN. They don't need Microsoft's help.
 
It's not just Excel; almost all computer software works this way. As
Chip Pearson's site noted, Excel (and your Pentium, which actually does
the arithmetic) follows the IEEE standard for double precision. The
only one of your nontrivial decimal fractions that can be exactly
represented is 0.75. The rest must be approximated (much as 1/3 must
can only be approximated as decimal fraction. You presumably have no
qualms about the inability to represent 1/3 exactly in decimal. These
approximations are no less accurate, the just occur with numbers that
you are not used to thinking of as requiring approximation. When you
calculate with approximate inputs, getting an approximate result should
be no surprise.

Help for "Excel specifications and limits" subtopic "Calculation
specifications" simplifies the IEEE standard by noting that Excel is
limited to 15 digit accuracy. Without understanding binary
representations, you can use this simplification to understand by
thinking of your problem as:

-2900000.00000000??????
35848.7500000000????
21548.4300000000????
1750.00000000000???
313.330000000000??
423.330000000000??
28188.6100000000????
2900.00000000000???
1450.00000000000???
2030.00000000000???
50.0000000000000?
500.000000000000??
26.0000000000000?
125.000000000000??
750.000000000000??
25.0000000000000?
2100000.00000000??????
5700.92000000000???
200.000000000000??
648170.630000000?????
50000.0000000000????
-----------------------
0.00000000??????
which is entirely consistent with Excel's result of
0.000000000232830643653869
itself a 15 digit representation of the binary number who's exact
decimal representation is
0.00000000023283064365386962890625

These issues with finite precision binary approximations are integral to
computer calculations, and have been well understood for over half a
century (long before MS and Excel). The usual approaches for dealing
with it are

- Do integer calculations. Integers (<=15 digits) are exactly
representable, so if you mentally multiply everything by 100 before you
enter the numbers, then you will get exactly zero for the answer.

- Consider anything extremely close to zero to be indistinguishable from
zero.

- Round the final result to a number of decimal places (<=8 for this
problem) that are reliable.

Jerry
 
Back
Top