Conditional Number Format BUG

Detlef_Lewin  wrote: ``B6 has the value 0.99999999999999989. B7 has the value 1``, referring to the 17-digit decimal representation in the XML file, which is how "xlsx" and "xlsm" files are stored, in contrast to the 15 digits that Excel displays, an arbitrary limit.

And in another response a year ago, Detlef_Lewin referred to the explanation in 17-or-15-digits-of-precision .

Just to clarify:  it is just as misleading to say that Excel "stores" values with 17-digit precision, as it is to say that it "stores" values with 15-digit precision.

Excel does represent numeric values with up to 17 significant decimal digits in XML.

But that is because the IEEE 794 standard states that 17 significant digits (not 15) is necessary and sufficient to convert all binary values between binary and decimal representations with no loss of precision.

Nevertheless, the 17-digit representation is still only an approximation of the binary value.

Excel actually stores the exact 64-bit binary floating-point value into computer memory when the application is running.

The exact decimal representation of the binary value can have many more significant digits than even 17 digits -- up to 1022 fractional digits with 714 significant digits for the smallest positive binary value, which is 2^-1022, about 2.2250738585072013E-308 (in VBA, because VBA does not truncate decimal data input after 15 significant digits, as Excel does).

To demonstrate:

                     displays (16 dp)
B6: =0.85+0.06+0.09  1.0000000000000000
B8: =0.85+0.06       0.9100000000000000
B9: =B6-B8           0.0900000000000000

    XML representation   exact decimal representation of binary value
B6: 0.99999999999999989  0.999999999999999,88897769753748434595763683319091796875
B8: 0.90999999999999992  0.909999999999999,9200639422269887290894985198974609375
    -------------------
B9: 0.08999999999999997  expected based on XML appearance of B6 and B8; but wrong(!)

correct result:
    XML representation    exact decimal representation of binary value
B9: 0.089999999999999969  0.0899999999999999,6891375531049561686813831329345703125

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

If Excel truly "stored" only 17 decimal (significant) digits, we would expect that B9 is stored as 0.08999999999999997 in XML.

Instead, we see 0.089999999999999969 in XML. (Actually, 8.9999999999999969E-2.)

The reason is demonstrated by the exact decimal representations of the binary values, on the right.

(Caveat: The difference between approximate decimal and exact binary representations is not always so obvious, due to the "normalization" of 64-bit binary floating-point and due to the fact that Intel-compatible CPUs actually use non-normalized 80-bit binary floating-point for binary arithmetic.)

Enlightening !. Thank you Detlef_Lewin