Comparing a value with a difference that should be same is not

Question

I'm using Excel 2013 on Windows 7.

I did some Excel data checking using basic IF and some operators. And I encountered an issue that give me inconsistency in the result. I'm checking is:

1.24 = 66.64 - 65.4

Below is the formula I used and the result should be mathematically EQUAL, but it gives me NOT EQUAL:

=IF(1.24 = 66.64 - 65.4, "EQUAL", "NOT EQUAL")     ;Result: NOT EQUAL

I tried to modify the data slightly to:

=IF(1.24 = 56.64 - 55.4, "EQUAL", "NOT EQUAL")     ;Result: EQUAL

and I'm surprised that it return the correct result.

Is this a bug, and if so, is there any workaround I can use for similar checking?

Answer 1

Excel's 15 digit precision floating point errors do not happen for every number combination; only when a decimal is rounded up or down due to a limited num ber of decimals. Time values are notorious for these errors.

So your first set of numbers (1.24 = 66.64 - 65.4) generates a floating point error from being digitally rounded off at some point. The second set of numbers (1.24 = 56.64 - 55.4) happily does not exhibit this behavior.

        

You can see in the preceding image that is you expand the decimals enough the floating point error becomes apparent.

More at Floating-point arithmetic may give inaccurate results in Excel.

Answer 2

Sort of a bug - I suspect due to the limited precision, that in turn is a result of representing floating point numbers. The way ROUND this is to force some approximation, say:

=IF(ROUND(1.24,2) = ROUND(56.64 - 55.4,2), "EQUAL", "NOT EQUAL")