Floating-Point Rounding May Cause Incorrect IF Evaluation

ID: Q77974

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

The method used by Excel to store floating-point numbers sometimes produces very small rounding errors, particularly when fractional values are involved. One possible effect of this is that IF statements that compare two values for equality will return a result that is the opposite from what is expected.

While it still works within the confines of calculating in floating point, the method used by Excel 3.0 and 4.0 are slightly different from that used in earlier versions. As a result, some formulas will evaluate correctly in Excel version 3.0 but not in Excel versions 2.x.

MORE INFORMATION

For example, the following function entered in ANY version of Excel


   =IF(.5-.4-.1=0,"I did not eat Bill's nose","I ate Bill's nose")

will evaluate to "I ate Bill's nose" (FALSE), not the expected value "I did not eat Bill's nose" (TRUE).

However, this formula


   =IF(1.1+2.2=3.3,TRUE,FALSE)

will evaluate to TRUE (the correct answer) in Excel 3.0 and 4.0, but will evaluate to FALSE in earlier versions.

Workaround

A good rule with floating point calculations is to never compare two values for equality because exact floating point equality often won't happen. Instead, check if the difference between the two is within some tolerance.

This can be done easily by using the ROUND function on the floating point values in the IF function, as follows:

=IF(ROUND(.5-.4-.1,3)=0,"I did not eat Bill's nose","I ate Bill's nose")

This will now evaluate correctly.

For more information on this topic, query on the words:


   floating and point and rounding and excel

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 200-201, 126-127

Additional query words:

Keywords :
Version :
Platform :
Issue type :