software

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?
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.--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: www.excelforum.com/member.php...oamp;userid=29640
View this thread: www.excelforum.com/showthread...hreadid=504702Keith,

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

Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware----- Original Message -----
From: quot;Keithquot; gt;
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?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:

cpearson.com/excel/rounding.htm

HTH,
Elkarquot;Keithquot; wrote:

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


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

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

support.microsoft.com/kb/78113/en-us--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=504702This 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 quot;Excel specifications and limitsquot;
subtopic quot;Calculation specificationsquot;). 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
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

quot;Keithquot; wrote:

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

Thanks for all the info.

quot;Jerry W. Lewisquot; wrote:

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

You're welcome.

Jerry

quot;Keithquot; wrote:

gt; Thanks for all the info.