XL: Comparison of Values Does Not Return Correct Result

ID: Q48606

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0, 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

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:

Q78113 : 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

Keywords :
Version : WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0,7.0a,97; MACINTOSH:1.x,2.x,3.0,4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb