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:

Rich (BB code):

```
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.)