Strange result in Excel 2000

[ibertram]

Hello folks,

Have you encountered this in your Excel? I am using Excel 2000 SP3.

Let's say you have:
Column A1: 57.16
Column B1: 27.74
Column C1: 84.90

In Column D1, you have this formula: =($A$1+$B$1)-$C$1. Result would b
0
But if you have this formula in column E1: =IF(($A$1+$B$1)-$C$1) = 0
"it
is zero", "it is not zero"). Result would be "it is not zero". Strange
huh?

It seems that it only happens to this set of number.

Do you have the same result in the other Excel version?

Thank you

 

[Don Guillett]

might be true in all versions due to 15 digit. Use the ROUND function.

 

[Bernard Liengme]

This has to do with the way computers (not just Excel) store decimal numbers
in binary. Since a limited number of bits (0 and 1) are used there has to be
some inaccuracy - round-off error.
The example commonly given is trying to express 1/3 as a number 0.333333333;
clearly it cannot be 'exact'
For more detail see http://support.microsoft.com/default.aspx/kb/78113
best wishes

 

[Dave Peterson]

So you could change your formula to something like:

=IF(ABS(($A$1+$B$1)-$C$1)<=0.0000000000001,"pretty darn close to 0","nope")

 

[Jerry W. Lewis]

While =($A$1+$B$1)-$C$1 returns zero, =(($A$1+$B$1)-$C$1) does not, and
it is this later calculation that IF() is basing its decision on.

None of your numbers have exact binary representations, and hence must
be approximated (just as 1/3 must be approximated in decimal). The
(IEEE standard) binary approximations to the numbers involved in the
final subtraction are
84.8999999999999914734871708787977695465087890625
-84.900000000000005684341886080801486968994140625
--------------------------------------------------
-0.0000000000000142108547152020037174224853515625
so Excel is exactly correct (given the approximations to the inputs) to
report the answer for =(($A$1+$B$1)-$C$1) as -1.4210854715202E-14, and
IF is correct to report that the result is not zero. And almost all
other general purpose software will behave in the same manner. The real
mystery is why =($A$1+$B$1)-$C$1 claims that the result is zero.

The key to that mystery is hidden in the knowledge base article (78113)
that Bernard referenced. Where it says that "Excel 97, however,
introduced an optimization that attempts to correct for this problem"
[that finite precision binary math sometimes produces correct but
unexpected results when subtracting numbers would be equal in decimal
representations]. What they did is to assume that if a final
subtraction involves two numbers that are equal to at least 15 decimal
digits, then Excel will arbitrarily zero the result on the assumption
that the non-zero difference is residue from binary approximations.
This arbitrary zeroing does not occur if the subtraction is not the last
operation (as when the last operation is surrounding parentheses, or
within an IF function) since the required assumption may be wrong, in
which case the arbitrary zeroing would reduce accuracy that introduced
inaccuracy could be magnified by subsequent calculations.

IMHO this "optimization" was a mistake, since it fails to eliminate the
situation it was aimed at, and instead makes Excel's math appear
inconsistent even to those who do understand the binary issues.

Jerry