Inconsistent "greater than" and "less than" calculations

[wpryan]

Hello, I have a column of cells into which I want to calculate some basic pass/fail criteria based on this formula

=IF(ISBLANK(C28),"",IF(D28>0.1,"Fail","Pass"))

The data for D28 is coming from another calculation, which is working correctly. If the result of the calculation is exactly 0.1, then most of the time I get a "Pass" return - which is incorrect (0.1 is not greater than 0.1). I say most of the time because although it happens this way most of the time, there are certain cells which give the correct answer.


..any idea what's going on?

 

[Norie]

Have you increased the no of decimal places to see what the exact value(s) are?

 

[wpryan]

Yes, I increased now to seven decimal places. number is 0.10000000

 

[Rick Rothstein]

Yes, I increased now to seven decimal places. number is 0.10000000

What number to you get if you increase it to 14 decimal places? By the way, we are talking about the calculated values, not the fixed 0.1 value.

 

[Rick Rothstein]

Cross-posted here...

Inconsistent "greater than" and "less than" calculations

 

[wpryan]

Hmmm. It seems that at the 15th decimal place, I get the number 1 (therefore, 0.100000000000001). How'd that happen!?

 

[wpryan]

The "trailing 1" (I'll call it for lack of a better term) is in the cell which calculates the 0.100 with this formula:

=IF(OR(ISBLANK(C29)),"",ABS(C29-A29))

Nothing odd about the reference cells (I took them out to 24 decimal places, and no "trailing 1's")

I changed the formula to a simple =C29-A29, but the "trailing 1" remains, therefore problem still remains.

what in the world is going on?!

 

[wpryan]

Hi Rick, yes I put it there as well, I should have mentioned it in this post. Sorry, I was getting desperate!

 

[Norie]

The problem is caused by the way computers store decimals.

 

[Rick Rothstein]

Hmmm. It seems that at the 15th decimal place, I get the number 1 (therefore, 0.100000000000001). How'd that happen!?

The value 0.1 does not have an exact binary (what computers use) representation, so values that calculate to it end up getting rounded, sometimes up, sometimes down depending on the calculation and sometimes the order of calculations... the "1" you see at the end is the result of that rounding. Why don't you round your calculated values to 10 decimal places and then compare that rounded value to see if it is greater than 0.1 or not.