SOLVED

# Conditional Number Format BUG

Highlighted
Occasional Contributor

# Conditional Number Format BUG

Excel seems to disagree with the commutative property of addition.

Both cells B6 and B7 have the same conditional number formatting: [Red][<1]0%;[Blue][>1]0%;0%

And the same formula except for the order of the factors:

B6's formula is =0.85+0.06+0.09

B7's formula is =0.85+0.09+0.06

Content for both cells is 100%, but B6 is shown in red (suggesting it is less than 1 or 100%). B7 is correctly rendered in a black font.

I tried different combination with variable results. I guess it's better to stay away from conditional number formatting

Excel 365 MSO (16.0.12827.20328) 64-Bit

8 Replies
Highlighted
Best Response confirmed by BilloCL (Occasional Contributor)
Solution

# Re: Conditional Number Format BUG

Congratulations. You just found out about the floating point error:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...

https://docs.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info

B6 has the value 0.99999999999999989.

B7 has the value 1.

Highlighted

# Re: Conditional Number Format BUG

Enlightening !. Thank you @Detlef Lewin

Highlighted

# Re: Conditional Number Format BUG

<p><li-user login="BilloCL" uid="724081"></li-user>&nbsp;if you never enter anything beyond the 2nd decimal, use round(...., 2)&nbsp;</p>
<p>&nbsp;</p>
Highlighted

Highlighted

# Re: Conditional Number Format BUG

Thank you @wsantos . Rounding works, either in each factor or applied to the whole sum. Excel's funky floating point arithmetic.

Highlighted

# Re: Conditional Number Format BUG

It was. Applying ROUND() to the adding factors worked.

Highlighted

# Re: Conditional Number Format BUG

a variant to @wsantos answer just change the conditional formatting to be <0.9999 and >1.0001

Highlighted

# Re: 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.0000000000000000B8: =0.85+0.06       0.9100000000000000B9: =B6-B8           0.0900000000000000`
`    XML representation   exact decimal representation of binary valueB6: 0.99999999999999989  0.999999999999999,88897769753748434595763683319091796875B8: 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 valueB9: 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.)