Sum does not sum to zero - leaves very small remainder

[Guest]

Hi,
I'm using SUM on a large spreadsheet of dollar type amounts that should only
have information to 2 decimal places and should sum to zero.

The problem is the sum function sums to a small remainder - in this case :

0.000000000069121597334742500000

I've looked at each value and none of them have any decimal values greater
than 2 decimal places.

Any ideas about why this is occuring ?

Regards
Michael

 

[Guest]

When dealing w/decimals, it may be necessary to use the round function. See
Chip's site for discussion about this issue.

http://www.cpearson.com/excel/rounding.htm

 

[Guest]

NG says the post wasn't received - so will try again:

When dealing w/decimals, it may be necessary to use the round function. See
Chip's site for discussion about this issue.

http://www.cpearson.com/excel/rounding.htm

 

[Guest]

Excel (and almost all other software) does binary math. In binary, the only
2-decimal fractions that can be exactly represented are .00, .25, .50, and
..75. All other 2-decimal fractions can only be approximated as a binary
fraction, just as 1/3 can only be approximated as a decimal fraction.

In 4-figure finite precision decimal, 1 - 1/3 -1/3 -1/3 would be 1 -0.3333
-0.3333 -0.3333 = 0.0001. In the same way, when you do finite precision
binary math, there can be predictable minor departures from infiinte
precision, such as you have seen.

Since the issue is approximations to binary numbers that need infinite
precision to represent exactly, and since you are only adding and subtracting
2-decimal fractions, rounding results to 2 decimal places does no violence to
the calculations and can avoid the impact of the binary approximations.

Jerry