When I use a function (Col A) to compare a calculated difference (Col C) with the same number typed in (Col B), I get inconsistent results.


the formula being =B2=C2 0.04 =D2+E2 -0.61 0.65

Using other comparible formulas (=IF types) yield the same result.
There is a pattern but it changes


Has anyone any ideas? This has dashed my confidence in Excel.

Attached Files

Difference error in Excel.xlsx (34.1 KB, 11 views)

Hi

What occurs in your sheet is known as floating point error (search with google, it will come up with a lot of links - though most of them very difficult to understand, so you will have to look for your own).
Briefly explained:
Some numbers (the most famous are 0.1 and 0.01) cannot accurately represented in binary. But that's the way computers operate, and when transferring the binary to decimal the error occurs.
How to make it visible:
That's - in my opinion - the annoying part with excel. The standard cell format usually doesn't display the error. But if you use the number format with 16 digits, it will show you the real value of the cell. That's for C54 = 0.0399999999999999
How to fix the problem:
In my opinion the best choice in case you need for some reason the accuracy is using the function ROUND() by rounding to the required digits.

Thank you. A great explanation, I understand.