Jul 09 2020 01:28 PM
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
Jul 09 2020 02:40 PM
SolutionCongratulations. You just found out about the floating point error:
https://docs.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
B6 has the value 0.99999999999999989.
B7 has the value 1.
Jul 09 2020 03:24 PM
Jul 09 2020 04:25 PM
Looks like floating point error https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu... in custom format handling
Jul 09 2020 04:45 PM
Thank you @wsantos . Rounding works, either in each factor or applied to the whole sum. Excel's funky floating point arithmetic.
Jul 09 2020 04:46 PM
It was. Applying ROUND() to the adding factors worked.
Thank you for your reply @Sergei Baklan
Jul 09 2020 04:46 PM
a variant to @wsantos answer just change the conditional formatting to be <0.9999 and >1.0001
Jul 11 2020 12:53 AM - edited Jul 11 2020 03:14 PM
@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.)
Jul 09 2020 02:40 PM
SolutionCongratulations. You just found out about the floating point error:
https://docs.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info
B6 has the value 0.99999999999999989.
B7 has the value 1.