IF function Bug?

[Dekazer]

My IF function =IF(SUM(A1:A11)=A12, " ", "Error") is not working
properly. I've used this function for 2 months now and just tonight it
failed. When I say failed I mean the function returns false when the
SUM=A12. I even tested it on another workbook. It only fails with the
following jumble of numbers. I'm not sure if it has to do with the
numbers being negative or if it's a bug. I'm using Excel 2000
(9.0.2720). Thanks in advance for the help.

$480.02 A1
($4,429.40)
$2,102.82
$357.91
$100.00
$231.80
$424.02
$436.95
$84.50
($9.54)
($50.64) A11
($271.56) A12

 

[Ron Rosenfeld]

My IF function =IF(SUM(A1:A11)=A12, " ", "Error") is not working
properly. I've used this function for 2 months now and just tonight it
failed. When I say failed I mean the function returns false when the
SUM=A12. I even tested it on another workbook. It only fails with the
following jumble of numbers. I'm not sure if it has to do with the
numbers being negative or if it's a bug. I'm using Excel 2000
(9.0.2720). Thanks in advance for the help.

$480.02 A1
($4,429.40)
$2,102.82
$357.91
$100.00
$231.80
$424.02
$436.95
$84.50
($9.54)
($50.64) A11
($271.56) A12

It's not actually a bug in Excel. Rather it is a consequence of the IEEE
standard used by Excel (and most other spreadsheets) requiring that numbers be
stored in binary format. Some numbers cannot be exactly represented as a
binary number -- akin to the problem of trying to represent certain fractions
as a decimal. For example 1/3 cannot be exactly expressed as a decimal no
matter how many decimals you use. So SUM(1/3,1/3,1/3) will be
2.999999999999999...7 rather than 3 if using decimal storage.

There are several workarounds. One that should work all the time would be to
look for the two numbers to be different by some small amount (less than the
desired precision of your calculation. One of the mathematicians here could
give you information as to how small that number needs to be in relation to the
desired precision.

=IF((ABS(SUM(A1:A11)-A12))<10^-10, " ", "Error")

will work.

Microsoft discusses this issue in Microsoft Knowledge Base Article - 214118.
They suggest checking Tools/Options/Calculation/Precision as Displayed as a
workaround. However, that doesn't work for your formula -- probably because
the SUM function still uses 15 digit precision.

There may be other methods that will work equally well.


--ron

 

[J.E. McGimpsey]

It's not a bug, it's an inherent limitation of the way binary math
is performed.

Just as not all numbers can be represented exactly in decimal (i.e.
1/9 = 0.111111...), not all numbers can be represented exactly in
binary (the computer's native language).

XL (and every other commercial spreadsheet) use IEEE double
precision floating point math, which uses techniques which minimize
the errors due to lack of precision, but they can't eliminate them
entirely.

So the problem here is that with that particular group of numbers
(and any number of other combinations), there's a difference in the
12th decimal place:

=SUM(A1:A11)

displays as ($271.56) to two decimal places, but when expanded to 12
decimal places (XL's precision is to 15 digits) is

($271.559999999999)

which is not exactly = ($271.56)

Couple of workarounds:

1) Use the ROUND() function for the comparison:

=IF(ROUND(SUM(A1:A11),2)=ROUND(A12,2), "", "Error")

the second ROUND() is not necessary if A12 is directly entered, but
is required if A12 contains a calculation.

2) Equivalently, compare to an arbitrarily small difference:

=IF(ABS(SUM(A1:A11)-A12)<0.0001, "", "Error")

3) You could also, since integer arithmetic is exact, multiply all
your values by 100, then divide your final results by 100 when
displaying them.

See

http://cpearson.com/excel/rounding.htm

for a more extensive explanation.

 

[Jerry W. Lewis]

The only thing I can add to the previous two posts explaining that this
is expected and due to finite precision binary representation of numbers
in computers is that
=SUM(A1:A11)+271.56
is not zero.

The only numbers in your column that have exact binary representations
are 100 and 84.5; the rest must be approximated. The math and the IF
comparison are exact, but they are based on approximate input numbers.
The easiest way to think about this is to note that the documented
precision of Excel is 15 digits; that means that your problem is
actually (using ? to indicate possible approximation discrepancies)

480.020000000000???
-4429.40000000000????
2102.82000000000????
357.910000000000???
100.000000000000???
231.800000000000???
424.020000000000???
436.950000000000???
84.5000000000000??
-9.54000000000000?
-50.6400000000000??
----------------------
-271.56000000000????

=SUM(A1:A11)+271.56 takes this result and adds

271.560000000000???
to get
0.00000000000????
which Excel calculates as
0.000000000000512...

Since you never give anything beyond the second decimal place, and are
only adding and subtracting, any results beyond the second decimal place
are due to binary approximations. That leads to Ron's recommendation
for testing for small differences, or J.E. McGimpsey's recommendation
about rounding to minimize approximation junk.

Jerry