Formula if(cell-cell<>0;"ERROR";"OK") just works with some numbers

[IreneFoncillas]

Hi all,

I am working with a formula that is driving me crazy... it seems to work, but if I change one decimal, then does not work properly.

I have prepared two examples to explain better:

In Example 1, when I use the formula IF(C5-C7<>0;"ERROR";"OK"), it says "ERROR", which means that result is not equal to zero (but it is indeed).

In Example 2, I just changed one decimal of Number 2, and it works!

All cells are Number format.

Does anyone knows, where the problem could be? Thank you all,

 

[i_nth]

You have a floating point precision error. That is, C5-C7 equals 2.84E-14 rather than zero. You can see the error if you use the Evaluate Formula tool.
Instead of testing if the value is zero, test if it is sufficiently close to zero for your purpose, something like:
=IF(ABS(C5-C7)>=0.000001,"Error","OK")

 

[joeu2004]

With your examples, the following would fix the problem:

=IF(C5<>C7, "ERROR", "OK")

But more generally, the better solution is to round calculations to the precision that you expect to be accurate. For example, ROUND(SUM(C2:C3),2)

The problem arises because most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel and most applications use internally. Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the value. That is why, for example, IF(10.01-10=0.01,TRUE) returns FALSE(!).

Those issues often cause infinitesimal differences between calculations that have identical results mathematically.

However, the inconsistencies arise because of dubious tricks that Excel plays, in an ill-conceived attempt to hide such infinitesimal differences.

For example, C5=C7 returns TRUE, even though C5-C7 (in some contexts) returns an infinitesimal difference, because Excel rounds the left and right operands of comparison operators (=, <>, >, etc) to 15 significant digits, just for the comparison. In contrast, MATCH(C5,C7,0) returns #N/A, indicating that their exact binary values differ.

And =C5-C7 returns exact zero (0.00E+00 when formatted as Scientific) because Excel arbitrarily replaces the actual difference with exact zero when operands of the last subtraction of a formula (not an expression) are "close enough" (and not zero). We can see the actual difference with =SUM(C5,-C7). It displays 2.84E-14 when formatted as Scientific or General.

 

[i_nth]

15 significant digits

Although Excel displays 15 significant figures, it uses 17 significant figures for calculations. If you have a look at the xml within an Excel xlsx file, then you can see the 17 significant figures stored for each cell.

 

[joeu2004]

Although Excel displays 15 significant figures, it uses 17 significant figures for calculations. If you have a look at the xml within an Excel xlsx file, then you can see the 17 significant figures stored for each cell.

Yes, that is my mantra (smile). But actually, that is not quite accurate.

In the XML file, Excel represents the exact binary value with 17 significant digits because that is sufficient to convert between decimal and binary with no loss of precision, as explained in the IEEE 754 standard. (And for the record, the standard never mentions 15 significant digits.)

But even the 17-digit representation is just an approximation. If we used the 17-digit decimal representation, we could not replicate the results of many calculations.

Excel actually uses the full binary precision for calculations. The exact decimal representation of the binary value can have many more than 17 significant digits (*).

And in fact, Intel-compatible CPUs use an 80-bit binary floating-point respresentation for the calculations per se. Excel converts the result of each 80-bit calcuation to 64-bit. VBA uses the 80-bit result in some contexts. Sometimes, that gives rise to different results for the same expression in Excel and VBA.

(*) But in many cases, we cannot use even the exact decimal representation to replicate a calculation. Besides the 80-v-64-bit complication, "normalization" of binary operands also complicates things. TMI. Sorry.

----

Be that as it may, the real point is: you quote and comment on my reference to 15 significant digits here out of context.

I wrote: ``Excel rounds the left and right operands of comparison operators (=, <>, >, etc) to 15 significant digits, just for the comparison``. I said nothing about calculations.

And that is the explanation for why C5<>C7 returns TRUE, but C5-C7<>0 returns FALSE in Irene's example.

Yes, the exact decimal representations of the binary values are:

C5, =SUM(C2:C3): 252.030000000000,02955857780762016773223876953125
C7, 252.03:      252.030000000000,001136868377216160297393798828125
C5-C7:             0.0000000000000284217094304040,07434844970703125

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

Excel uses the exactly binary difference in the context C5-C7<>0.

But in the context C5<>C7, Excel compares 252.030000000000 (C5) and 252.030000000000 (C7). That is, each operand is rounded to 15 significant digits. In contrast, MATCH(C5,C7,0) compares the exact binary values, which are different, of course.

Also, in the context =C5-C7, Excel replaces the exact binary difference with exact zero (0.00E+00) arbitrarily, because Excel decided arbitrarily that the binary values in C5 and C7 are "close enough".

(I could explain what "close enough" means; but it is complicated (read: TMI). It is not as simple as: they are the same when rounded to 15 significant digits. Sigh.)

 

[IreneFoncillas]

Thank you both for your detailed explanations! Now I understand why the formula did not work, and with ROUND, it works