Re: Excel Bug - Excel Geeks Unite!!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 *** wrote:

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" <post_a_reply@xxxxxxxxxxxxx> wrote in message
news:42A56CC0.7090803@xxxxxxxxxxxxxxxx

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

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??




.

Quantcast