Microsoft KB Archive/48606

= Comparison of Values Does Not Return Correct Result =

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.