Excel Bug - Excel Geeks Unite!!

[Guest]

Ok here is whats going on,
227.82 227 0.75 0.05 0.02 227.82 False

inputed 227.82
then sum 227+.75+.05+.02=227.82
then the last cell is an IF THEN statement
If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True")
Tell me why excel insists that this statement is false??

 

[Jim Cone]

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

 

[Bob Umlas]

Nature of binary arithmetic. 227.82 doesn't REALLY com out that way --
something like 227.81999999999994 or so. When you evaluate the formula
(using tools/formula auditing/evaluate formula), you'll see that the simple
subtraction comes out 2.8421709430404E-14, which isn't zero.
Change your formula to something like =IF(ROUND(Cell1-cell5,5)=0,TRUE,FALSE)

Bob Umlas
Excel MVP

 

[Jerry W. Lewis]

Most decimal fractions (including .82, .05, and .02) have no exact
finite binary representation, just as 1/3 has no exact decimal
representation. Since your inputs must be approximated, the results are
only approximate. Programming around this issue in floating point
calculations has been standard for over half a century (long before
Excel was a gleam in Bill's eye).

To understand the problem intuitively, consider a hypothetical computer
that does decimal arithmetic with 4 significant figures.
1 = 1/3 + 1/3 + 1/3
but on this hypothetical computer, then 1/3 = 0.3333 so
1/3 + 1/3 +1/3 = 0.9999 <> 1

Excel (and almost all other general purpose software) uses IEEE double
precision binary arithmetic. The IEEE double precision approximation
for 227.82 is
227.81999999999999317878973670303821563720703125
the approximation for 0.05 is
0.05000000000000000277555756156289135105907917022705078125
the approximation for 0.02 is
0.0200000000000000004163336342344337026588618755340576171875
Hence in binary 227+0.75+0.05+0.02 is greater than the binary
approximation to 227.82. That is why
=(227.82-(227+0.75+0.05+0.02))
returns -2.8421709430404E-14 (the correct result of the binary
operations) instead of zero.

You will get similar results in almost all general purpose software,
unless they apply some sort of fuzz factor to the calculations. Excel
applies a fuzz factor if the subtraction is the last operation, so that
=227.82-(227+0.75+0.05+0.02)
will return zero, but this fuzz factor does not apply inside an IF()
function.

Give the nature of the issue, two simple and theoretically correct way
to do your IF would be
=IF( ROUND(cell1-cell5,2)=0, TRUE, FALSE)
=IF( ABS(cell1-cell5)<epsilon, TRUE, FALSE)
where epsilon is a suitably small number (<0.01 in this case).

Jerry

 

[Jack Sheet]

It would have been nice if Excel had afforded the user an opportunity to
round "globally" all cells to a specified precision, say 13 significant
figures (user-definable). That way if you are confident that no calculation
in a workbook should create a 14+ significant figure other than zero it
would automatically strip out the errors arising from the binary conversion.
Of course you can do this by manually inserting an =ROUND(ref,13) function
around every cell that contains a formula (OK, that would round to decimal
places rather than significant figures), but what a palava when with a bit
of design work a single check-box could do it. The effect would be
different from the kludgy "precision as displayed" option currently
available.

I remember when I first came across this effect many years ago I was
surprised that when I imported the offending file into SuperCalc it seemed
to strip out some of the rounding errors automatically that Excel generated.
Not sure how SuperCalc managed that.

 

[Jerry W. Lewis]

Tools|Options|Calculation|Precision as displayed
Will round all values to cell format precision.

Rounding to a specified number of significant figures does not
necessarily solve the problem (see my previous decimal example involving
1/3.

The last time I saw SuperCalc, many packages were using custom floating
point number formats, which would allow the possibility of carrying many
guard digits to reduce the likelihood of seeing these issues (I don't
know if that is in fact what SuperCalc did). That situation was
potentially frustrating to serious numerical analysts, since it meant
that there was far more precision available than you were allowed to
use. These days, almost everyone (including Excel) follows the IEEE
standard. The few exceptions use binary coded decimal (BCD) or
adjustable precision for special purpose applications.

Given the nature of this issue, I would tend to distrust any current
package that does not exhibit the normal effects of finite precision
binary approximation, unless the reason is clearly documented. To do
hide these issues while doing binary math, the package would have to
make assumptions that may not be valid. I generally prefer that
software not try to think for me.

Jerry

 

[Jack Sheet]

Points well taken. Although under my suggestion you would still have the
option to untick the checkbox that causes the global rounding, and thereby
revert to IEEE standard.