Errata....

On Dec 26, 11:50 pm, I wrote:
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 -----

On Dec 26, 11:50*pm, joeu2004 wrote:
On Dec 26, 2:29*pm, abracadabra

wrote:
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.600000000000000088817841970012523233890533447265 625, 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.