Error in IF statement with greater than or equal to

Getting an error in a simple formula (using Excel 2007)

Formula: =IF(B1<=0.05,"within limits","out of limits")

Where B1 is a simple calculated value (subtraction between two numbers)

Getting the same value but using different starting numbers is producing different results from the formula. I have attached a file in which i just looked at 100 examples (0-100) and have errors between 8-31.

Any thoughts as to why I would get different results even thought the calculated value never changes? Something with rounding in the background?

Driving me nuts!

Thanks for the help!

I don't see any errors on your spreadsheet. As an alternative try this formula

Formula:

Please Login or Register  to view this content.

The issue stems from the fact that the computer is performing calculations in binary and we use decimal system. Sometimes there is not an exact match between them. If you select column B and format cells> number with 15 decimals, you will see the issue.

You can modify your formula either in B
=ROUND(C2-D2,2)
or in A
=IF(ROUND(B2,4)<= 0.05......)
Does that help?

Hi,

You've hit the well known 'arithmetical precision' factor.
If you choose to show say B7 to 16 decimal places you'll see the number is actually 0.0500000000000007
B6 similarly is 0.4999999999999800

Change your formula to

Formula:

Please Login or Register  to view this content.