Article ID: 48606
Article Last Modified on 11/16/2006
APPLIES TO
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 98 for Macintosh
This article was previously published under Q48606
SYMPTOMS
In Microsoft Excel, two sums that appear to be equal may return either FALSE when compared (as in an IF statement) or an incorrect value when a calculation is performed based on the two values.
CAUSE
This problem occurs because of the precision of your computer, which must represent and manipulate numbers in binary. Microsoft Excel compares the exact binary representation of the numbers rather than their decimal equivalents, which are displayed on the screen. Therefore, rounding errors can occur in the binary representation of the numbers that are not evident when comparing the decimal values visually.
WORKAROUND
To work around this behavior, use any of the following methods.
Method 1
Have Microsoft Excel compare the displayed values of the numbers, rather than their exact binary value. (Note that this procedure affects only directly comparing values that are displayed on the worksheet; it does not
affect rounding errors that can occur in comparing formulas referring to those values.)
To do this, use the steps appropriate for your version of Microsoft Excel.
Versions 5.0 and Later:
Click Options or Preferences on the Tools menu, and click the Calculation tab. Under Workbook Options, select the Precision As Displayed check box, and then click OK.
Version 4.0:
Click Calculation on the Options menu, and then select the Precision As Displayed check box. Click OK.
Versions 2.2 and 3.0:
Click Calculation on the Options menu, and then select the Precision As Displayed check box. For example, to ensure no rounding errors occur in the formula
A1: =IF(B1=B2*-25,TRUE,FALSE)
select the Precision As Displayed check box, and break the formula into two cells as follows:
A1: =B2*-25 A2: =IF(B1=A1,TRUE,FALSE)
Method 2
Use the ROUND() function to round the number to the desired number of digits. The following example rounds the two values to the second decimal place:
A1: =IF(ROUND(B1,2)=ROUND(B2*-25,2),TRUE,FALSE)
Method 3
Compare the absolute value of the difference between the values to a number smaller than the significant difference. For example, the following formula checks to see that the two values are within .001 of each other (which is insignificant for the numbers used, but greater than a rounding error):
A1: =IF(ABS(B1-(B2*-25))<.001,TRUE,FALSE)
MORE INFORMATION
For additional information, please see the following article in the Microsoft Knowledge Base:
78113 : XL: Floating-Point Arithmetic May Give Inaccurate Results
Additional query words: 2.2 2.20 3.0 4.00 5.00a 97 98 XL98 XL97 XL7 XL5 XL4 XL3 XL
Keywords: kbprb KB48606
