SUM returns wrong result

[MarkN]

I am using the SUM function to add positive and negative currency values. The
numbers are 2053.12, -1272.15, -260, -420.97 and -100. Excel returns a result
of -0.000000000000227. The sum of the negative numbers is -2053.12 and the
positive number is +2053.12 so I was expecting 0.00.

I can use the ROUND function to sort this out but I am very interested in
knowing what causes this to happen and if there is something I can do so that
I can just use the SUM function to return the correct result.

 

[Bob Phillips]

Mark,

Take a look at this page http://www.cpearson.com/excel/rounding.htm

A good previous thread at http://tinyurl.com/8jjf4

Not directly related, but an interesting thread at http://tinyurl.com/d4475

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Jerry W. Lewis]

As Bob's links explain, computers do binary math. None of your floating
point numbers have exact binary representations, and hence must be
approximated. Approximation to inputs implies that the output is
necessarily only approximate.

The binary approximation to 2053.12 is smaller than the exact value.
The binary approximations to 1272.15 and 420.97 are larger than the
exact value. Since only the first one has a positive sign in your
calculation, the approximate result will be less than the exact result.

Specifically
2053.1199999999998908606357872486114501953125
-1272.15000000000009094947017729282379150390625
-260
-420.970000000000027284841053187847137451171875
-100
-----------------------------------------------
-0.000000000000227373675443232059478759765625

As a practical matter, you don't need to determine the exact binary
approximations to predict the accuracy of the final answer. Help for
"Specifications" clearly documents Excel's limit of 15 digit accuracy; thus
2053.12000000000???
-1272.15000000000???
-260
-420.970000000000??
-100
-------------------
0.00000000000???
consistent with actual result
-0.000000000000227

This is not just an Excel issue, it is common to all software that does
binary math (almost all software), and as Bob's link to Chip Pearson's
web site shows, the specific accuracy is determined by the IEEE standard
for double precision.

Jerry

 

[MarkN]

Thanks very much Bob.
--
Cheers,
MarkN

Mark,

Take a look at this page http://www.cpearson.com/excel/rounding.htm

A good previous thread at http://tinyurl.com/8jjf4

Not directly related, but an interesting thread at http://tinyurl.com/d4475

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[MarkN]

Thanks Jerry,

I might not like it, but at least I can understand it!

 

[Jerry W. Lewis]

You're welcome, glad it helped.

Jerry