SUM returns wrong result

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.
--
Thanks,
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> --
> Thanks,
> MarkN

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 wrote:

> 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.

Thanks very much Bob.
--
Cheers,
MarkN


"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > --
> > Thanks,
> > MarkN
>
>
>

Thanks Jerry,

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


"Jerry W. Lewis" wrote:

> 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 wrote:
>
> > 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.
>
>

You're welcome, glad it helped.

Jerry

MarkN wrote:

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