Weird Arithmetic Error

[Keith]

Can someone please explain the following weird arithmetic error?

enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
0. Instead I get 7.10543E-13.

Any clues?

[wjohnson]

Dont know why but if I format all Cells to NUMBER instead of GENERAL then everything looks ok. If I SUM A2:A25 then Add A1, everything looks OK. If I add -2002.08 in cell 25 and then do the sum of A1:A25 everything looks OK. The (-) as the first item is what is messing up the formula, unless you change the Column to Number.

[daddylonglegs]

Can someone please explain the following weird arithmetic error?

enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
0. Instead I get 7.10543E-13.

Any clues?

This is a known issue caused by the way Excel calculates using "floating point arithmetic" - see this link for more details

http://support.microsoft.com/kb/78113/en-us

[Elkar]

If you format your results cell as a Number, then your answer should look
right. I could try to explain why this is, but the following article will do
a much better job:

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

HTH,
Elkar


"Keith" wrote:

> Can someone please explain the following weird arithmetic error?
>
> enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
> A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
> 0. Instead I get 7.10543E-13.
>
> Any clues?
>

[Jim Cone]

Keith,

Chip Pearson takes a pretty good whack at it here...
http://www.cpearson.com/excel/rounding.htm

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


----- Original Message -----
From: "Keith" <[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Tuesday, January 24, 2006 3:42 PM
Subject: Weird Arithmetic Error
Can someone please explain the following weird arithmetic error?
enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
0. Instead I get 7.10543E-13.
Any clues?

[Jerry W. Lewis]

This type of question occurs often enough to be considered an FAQ.

Excel's arithmetic is exactly correct, but the numbers that you entered had
to be approximated. The difference between what you got and what you
expected is due to those approximations, not the subsequent arithmetic.

Most terminating decimal fractions are non-terminating binary fractions,
hence the necessity of approximating your inputs. The accuracy of
approximation for Excel and almost all other general purpose software is
defined by the IEEE standard for double precision, as 15 bits, which can
guarantee 15 digit accuracy (see Help for "Excel specifications and limits"
subtopic "Calculation specifications"). Since -2002.08 has 4 digits to the
left of the decimal point, you can only rely on 11 decimal places in the
approximation, which is consistent with the result you obtained.

You can use the D2D function from
http://groups.google.com/group/micro...06871cf92f8465
to see that the approximations to your intputs are
-2002.079999999999927240423858165740966796875
83.4200000000000017053025658242404460906982421875
With intermediate rounding to 53 bits, the correct result of calculating
with these numbers is 7.10542735760100185871124267578125E-13, which Excel
correctly reports to its documented 15 digit limit as 7.105427357601E-13

Jerry

"Keith" wrote:

> Can someone please explain the following weird arithmetic error?
>
> enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
> A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
> 0. Instead I get 7.10543E-13.
>
> Any clues?
>

[Keith]

Thanks for all the info.

"Jerry W. Lewis" wrote:

> This type of question occurs often enough to be considered an FAQ.
>
> Excel's arithmetic is exactly correct, but the numbers that you entered had
> to be approximated. The difference between what you got and what you
> expected is due to those approximations, not the subsequent arithmetic.
>
> Most terminating decimal fractions are non-terminating binary fractions,
> hence the necessity of approximating your inputs. The accuracy of
> approximation for Excel and almost all other general purpose software is
> defined by the IEEE standard for double precision, as 15 bits, which can
> guarantee 15 digit accuracy (see Help for "Excel specifications and limits"
> subtopic "Calculation specifications"). Since -2002.08 has 4 digits to the
> left of the decimal point, you can only rely on 11 decimal places in the
> approximation, which is consistent with the result you obtained.
>
> You can use the D2D function from
> http://groups.google.com/group/micro...06871cf92f8465
> to see that the approximations to your intputs are
> -2002.079999999999927240423858165740966796875
> 83.4200000000000017053025658242404460906982421875
> With intermediate rounding to 53 bits, the correct result of calculating
> with these numbers is 7.10542735760100185871124267578125E-13, which Excel
> correctly reports to its documented 15 digit limit as 7.105427357601E-13
>
> Jerry
>
> "Keith" wrote:
>
> > Can someone please explain the following weird arithmetic error?
> >
> > enter the amount -2002.08 in cell A1. Enter the amount 83.42 in cells
> > A2-A25. In cell A26 calculate the sum of cells A1-A25. The answer should be
> > 0. Instead I get 7.10543E-13.
> >
> > Any clues?
> >

[Jerry W. Lewis]

You're welcome.

Jerry

"Keith" wrote:

> Thanks for all the info.