In Excel 2010, certain numbers in formul will not equal zero when they should.

Question

The formula we tried to use was =IF((38-G12-D12-J12-M12),"Error: Recalculate","OK: 38 Hours Total")

but when G12 was 36.7 and D12 was 1.3, the answer was not zero and the formula failed.

So tried again on another computer just simply A1 is 38, B1 is 36.7 & C1 is 1.3 then the formula A1-B1-C1 should equal zero but the result from Excel is -2.88658E-15 or -0.0000000000000028865798640254100000

Certain number combinations will not equal zero exactly.

Does anyone know about this glitch and if there is a fix for it?

It is a very small but quite problematic issue that you cannot trust excel to calculate exactly for certain numbers.

Thanks

Answer 1

This is known as the binary round off error and is present in most numeral apps other than apps like Mathematica

See any link below

To solve USE =IF((round(38-G12-D12-J12-M12, 12),"Error: Recalculate","OK: 38 Hours Total")

best wishes

IEEE 754

Chip's clear explanation

http://www.cpearson.com/excel/rounding.htm

Quoting Chip Pearson...

"It is important to note that these errors and limitations on fractional numbers are not really errors at all. Nor are they "bugs" in the programs. These are well-known and well-documented limitations of the floating point arithmetic systems in almost every software package and hardware device."

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

Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”

http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

What Every Computer Scientist Should Know About Floating Point

http://docs.sun.com/source/806-3568/ncg\_goldberg.html

Visual Basic and Arithmetic Precision

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1

Good reading from T Valko

http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

Others:

http://support.microsoft.com/kb/214118

http://blogs.msdn.com/b/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

http://docs.sun.com/source/806-3568/ncg\_goldberg.html

---

---