Two values not adding correctly

  • Thread starter Thread starter chitown29
  • Start date Start date
C

chitown29

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.
 
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
 
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/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1

Good reading from T Valko
http://blogs.msdn.com/excel/archive...es-excel-give-me-seemingly-wrong-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/office/default.aspx#tab=4
 
This is a common problem called rounding error. It can be avoided as follows:

=ROUND(664199.05-582911.61,2)
 
Back
Top