Excel nested IF with "AND" operator behaving weird

Question

I having a weird situation in Excel spreadsheet. I'm applying a simple nested IF statement as below:

= IF( AND(A1-INT(A1)>=0.3,A1-INT(A1)<=0.7),INT(A1)+0.5,
    IF( AND(A1-INT(A1)>=0,A1-INT(A1)<=0.2),INT(A1),
        IF( AND(A1-INT(A1)>=0.8,A1-INT(A1)<=0.9),INT(A1)+1, "NON-CHECKED"    )
       )
    )

Suppose if "A1" contains a value 32.9, the result should be 33. It works fine until the value of "63.9". As soon as A1 contains value of "64.9", it does not check the last condition and prints out "NON-CHECKED".

It's a strange thing that it works fine until a value of 63.9 and after that it starts become FALSE.

I tried the same formula in Office Suit and Google Docs too.

Am I missing something here ?

You can test the formula here:

Sample Spreadsheet

Thanks.

Answer 1

Please try replacing A1-INT(A1) with ROUND(MOD(A1,1),10). This seems to work for numbers less then 10^9. If you work with higher numbers, you should decrease precision.