Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=504702 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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" 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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? 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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=504702 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Arithmetic Error
You're welcome.
Jerry "Keith" wrote: Thanks for all the info. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Very weird error | Excel Worksheet Functions | |||
vlookup weird error | Excel Worksheet Functions | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Weird error messages | Excel Discussion (Misc queries) |