wrong result if function values over 256

A

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)
 
J

JE McGimpsey

abracadabra said:
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.
 
J

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.
 
J

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top