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" wrote in message
...
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.050000000000000002775557561562891351059079170227 05078125
the approximation for 0.02 is
0.020000000000000000416333634234433702658861875534 0576171875
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
Joey Bag O wrote:
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??
|