Two values not adding correctly

[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.

 

[Fred Smith]

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

 

[Bernard Liengme]

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

 

[Gary''s Student]

This is a common problem called rounding error. It can be avoided as follows:

=ROUND(664199.05-582911.61,2)