Less Than Equal To Fails at Edge

Computers can be funny about comparing real numbers. Sometimes if you calculate the same number two different ways, a computer will think it's two different numbers. This is a long explanation that I won't give you here, but here is the way around it in this case.

Formula:

Please Login or Register  to view this content.

This assumes that you will always have numbers with no more than two digits after the decimal point.

You could try rounding.

=IF(ROUND(A3-B3,2)<=B1, "Pass", "Fail")

By the way, =IF(A3-B3<=1, "Pass", "Fail") works for me in Excel when the values are entered manually.

Actually, it is Open Office that is "wrong" -- albeit perhaps more consistent than Excel with the fudging that both products do.

In a nutshell, the problem arises because Excel (and Open Office, I assume) represents numeric values using 64-bit binary floating-point.

The general work-around is to explicitly round calculations with decimal fractions, or whose result might have a decimal fraction, to the degree of accuracy that you expect. In this case, round 12.63-12 to 2 decimal places.

-----

In 64-bit binary floating-point, most decimal fractions cannot be represented exactly. Instead, they are approximated by the sum of 53 consecutive powers of 2 ("bits") times an exponential factor.

Moreover, the approximation of a particular decimal fraction might differ depending on the magnitude of the integer part. The integer part is represented by some of the 53 bits, leaving the sum of fewer bits to approximate the decimal fraction.

Consequently, binary computer arithmetic often results in infinitesimal differences compared to decimal arithmetic that we do on paper.

To add to the confusion, Excel formats (displays) only the first 15 significant decimal digits (rounded) that represent the binary value. Consequently, these infinitesimal differences seem to come from nowhere.

For example, 12.63 is displayed as 12.630000000000000000 when formatted with 18 decimal places; and 0.63 is displayed as 0.630000000000000000. But =12.63-12 is displayed as 0.630000000000001000.

The exact decimal representation of each binary value is:

Please Login or Register  to view this content.

I use period for the decimal point and comma to demarcate the first 15 significant digits.

-----

Finally, sometimes =A3-B3<=B1 does not work expected, but =A3-B3-B1 is exactly zero(!), which is 0.00E+00 when formatted as Scientific.

This is because Excel tries to compensate for the infinitesimal anomalies -- but only sometimes. In the second example, Excel replaces the actual arithmetic result with exactly zero because Excel arbitrarily deterimines that the last subtraction is "close enough" to zero.

But to demonstrate the inconsistency in this determination, note that =A3-B3-B1<=0 is FALSE(!).

In Excel (and apparently Open Office), comparison operators round the left and right expressions to 15 significant decimal digits (rounded) before comparing them. 12.63-12 rounds to 0.630000000000001, whereas 0.63 rounds to 0.630000000000000.

I also found that Google Sheets has the same problem.

Not surprising. This is an inherent bug/feature in all computer (floating point) computations, so I am not surprised that Google Sheets or Open Office or any other spreadsheet app exhibits the same problem. If you expand your research, you will find the same problem in all programming languages, when you choose a floating point data type for your variables. This is an inherent part of floating point calculations, and there is no permanent "fix" to it. If you are interested in further research (and Joeu2004's description in post #5 wasn't enough), I have links to other descriptions of this "problem" here: https://www.excelforum.com/groups/ma...nd-errors.html

You put a $ on your 0.01 in your latest post, suggesting that maybe these are financial figures you are looking at. One strategy to minimize the effect of floating point error is to use "integers" instead of decimal data. Many/Most programming languages have a "currency" (scaled integer) data type that treats all numbers as integers. Spreadsheets don't have a currency data type, but I suggested one way to simulate a currency data type in this post: https://www.excelforum.com/excel-gen...ml#post4784658

In a nutshell, if you were not aware of floating point error before, now you are. Now you can work on learning the strategies needed for minimizing the impact of floating point error on your calculations.