wrong result if function values over 256

[abracadabra]

very strange behavior of if function resulting in wrong result which might be
a bug.
try it out at your own, and try other values just remember the same
difference.

a1=300 b1=303,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK";"NOT OK")

the result of e1 should be "OK" but its "NOT OK"

a1=200 b1=203,6 c1=b1-a1 d1=3,6 e1=if(c1<=d1;"OK;"NOT OK")

the result of e1 is "OK"

What the hell is going on ? It does not help to format the cell's.
Have i found a bug, and if it is i will call it a MAJOR BUG which could be
the reson of world economic crisis ;o)

 

[JE McGimpsey]

What the hell is going on ? It does not help to format the cell's.
Have i found a bug, and if it is i will call it a MAJOR BUG which could be
the reson of world economic crisis ;o)

This isn't a bug, it's a byproduct of using binary math that nearly
every spreadsheet exhibits (granted, users who don't know how their
tools work may contribute to the world economic crisis, but that's a
different story..).

See:

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


What you're seeing is a small rounding error. If you need to compare
numbers, it's much better to use a range. e.g.:

E1: =IF(ABS(C1-D1)<0.000000001, "OK", "NOT OK")

Adjust the 0.0000000001 to suit. Of course, if you need better precision
than XL's 15 internal digits, you should be using a different package.

 

[joeu2004]

a1=300 b1=303,6 c1=b1-a1 d1=3,6
e1=if(c1<=d1;"OK";"NOT OK")
the result of e1 should be "OK" but its "NOT OK"

a1=200 b1=203,6 c1=b1-a1 d1=3,6
e1=if(c1<=d1;"OK;"NOT OK")
the result of e1 is "OK"

What the hell is going on ?

The problem is that Excel uses a standard binary format to represent
numbers, as do most applications. In binary, numbers with decimal
fractions (and extremely large integers) cannot be represented
exactly.

In your example, 3.6 is represented internally exactly as
3.600000000000000088817841970012523233890533447265625, whereas the
result of 300-303.6 is represented internally exactly as
3.6000000000000227373675443232059478759765625. As you can see, Excel
is correct in determining that (300-303.6)<=3.6 is false.

On the other hand, the result of 200-203.6 is represented internally
exactly as 3.599999999999994315658113919198513031005859375. In this
case, (200-203.6)<=3.6 is true. But that is merely coincidence, an
artifact of the way that binary arithmetic is performed by the
hardware.

The usual solution is to round expressions appropriately, where
"appropriately" means subject to the requirements of your
application. For your examples, ROUND(expression,13) does the trick
in both cases. But again, that is coincidence. If you only need
accuracy to one decimal place, use ROUND(expression,1) consistently.

Caveat: Rounding is not a panacea.

 

[joeu2004]

Errata....

the result of 300-303.6 is represented internally
exactly as
3.6000000000000227373675443232059478759765625.
[....]
the result of 200-203.6 is represented internally
exactly as
3.599999999999994315658113919198513031005859375.

Doh! Of course, I should have write 303.6-300 and 203.6-200
everywhere.


----- original posting -----

 

[Shane Devenshire]

Hi,

I thing that larger errors by users are responsible for the world economic
crisis, and if they had used Excel properly they might have gotten an
inkling of what could happen to them. Really, these number are off in the
14th or 15th decimal place - 0.000000000000034

not

5,000,000,000,000 (5 trillion dollars)

A lot of people bought houses far beyond theirs means and not by
$.0000000000003, more like $100,000.

Cheers,
Shane Devenshire