Microsoft KB Archive/127938

= Microsoft Knowledge Base =

MXL5: Formula Subtracting Equal Values Doesn't Return Zero
Last reviewed: September 12, 1996

Article ID: Q127938

The information in this article applies to:


 * Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS
In Microsoft Excel, when you enter a formula that subtracts the result of one cell from another cell with the same result, the value returned by the formula may not be 0 (zero).

CAUSE
This behavior occurs with the values 1000001, 10000001, and 100000001 if one of the values is a formula.

In the following example

A1: =1000001 A2: 1000001 A3: =A1-A2 cell A3 returns the value -2.328E-10 instead of the expected value 0. When you subtract the cell that contains the formula from the cell that contains the value, the result is a positive value. In the above example, if you enter the formula A2-A1, the result is 2.328E-10.

If you enter a formula that returns the difference between the values 10000001 and =10000001, the result is 3.725E-9. If you enter a formula that returns the difference between the values 100000001 and =100000001, the result is 2.98E-08.

Note, however, that Microsoft Excel does consider these values to be equal. That is, in the above example, if you enter the formula =A1=A2, the result returned is TRUE.

Note that this problem only occurs in Microsoft Excel for the Macintosh (68K version); this problem does not occur in Microsoft Excel for the Power Macintosh.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0a for the Macintosh.