If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Two values not adding correctly
In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61
the answer comes out 81,287.4400000001? Why is there a 1 at the end? This causes issues when building formulas to control two sets of information that should equal one another. |
#2
|
|||
|
|||
Two values not adding correctly
That's the way computers work. They work in binary, we work in decimal.
There are imprecisions in the translation process, as you've found out. If you want a specific precision, use the Round function, as in: =round(yourformula,2) Regards, Fred "chitown29" wrote in message ... In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61 the answer comes out 81,287.4400000001? Why is there a 1 at the end? This causes issues when building formulas to control two sets of information that should equal one another. |
#3
|
|||
|
|||
Two values not adding correctly
Never test if two values are equal with formulas like
=A1=B1 But use =ROUND(A1-B1,12)=0 or ABS(A1-B1)1e-12 This will get around the IEEE rounding errors If you want to delve deeper: Chip's clear explanation http://www.cpearson.com/excel/rounding.htm Floating-point arithmetic may give inaccurate results in Excel http://support.microsoft.com/kb/78113/en-us (Complete) Tutorial to Understand IEEE Floating-Point Errors http://support.microsoft.com/kb/42980 What Every Computer Scientist Should Know About Floating Point http://docs.sun.com/source/806-3568/ncg_goldberg.html http://www.cpearson.com/excel/rounding.htm Visual Basic and Arithmetic Precision http://support.microsoft.com/default...NoWebContent=1 Good reading from T Valko http://blogs.msdn.com/excel/archive/...g-answers.aspx Others: http://support.microsoft.com/kb/214118 http://docs.sun.com/source/806-3568/ncg_goldberg.html best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all meet again at http://answers.microsoft.com/en-us/o...ult.aspx#tab=4 "chitown29" wrote in message ... In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61 the answer comes out 81,287.4400000001? Why is there a 1 at the end? This causes issues when building formulas to control two sets of information that should equal one another. |
#4
|
|||
|
|||
Two values not adding correctly
This is a common problem called rounding error. It can be avoided as follows:
=ROUND(664199.05-582911.61,2) -- Gary''s Student - gsnu201003 "chitown29" wrote: In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61 the answer comes out 81,287.4400000001? Why is there a 1 at the end? This causes issues when building formulas to control two sets of information that should equal one another. |
Thread Tools | |
Display Modes | |
|
|