SOLVED

Conditional Number Format BUG

%3CLINGO-SUB%20id%3D%22lingo-sub-1512904%22%20slang%3D%22en-US%22%3EConditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1512904%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20seems%20to%20disagree%20with%20the%20commutative%20property%20of%20addition.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20cells%20B6%20and%20B7%20have%20the%20same%20conditional%20number%20formatting%3A%26nbsp%3B%5BRed%5D%5B%26lt%3B1%5D0%25%3B%5BBlue%5D%5B%26gt%3B1%5D0%25%3B0%25%3C%2FP%3E%3CP%3EAnd%20the%20same%20formula%20except%20for%20the%20order%20of%20the%20factors%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB6's%20formula%20is%20%3D0.85%2B0.06%2B0.09%3C%2FP%3E%3CP%3EB7's%20formula%20is%20%3D0.85%2B0.09%2B0.06%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EContent%20for%20both%20cells%20is%20100%25%2C%20but%20B6%20is%20shown%20in%20red%20(suggesting%20it%20is%20less%20than%201%20or%20100%25).%20B7%20is%20correctly%20rendered%20in%20a%20black%20font.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20different%20combination%20with%20variable%20results.%20I%20guess%20it's%20better%20to%20stay%20away%20from%20conditional%20number%20formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20365%20MSO%20(16.0.12827.20328)%2064-Bit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1512904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513072%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724081%22%20target%3D%22_blank%22%3E%40BilloCL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECongratulations.%20You%20just%20found%20out%20about%20the%20floating%20point%20error%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Faccess%2Ffloating-calculations-info%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Faccess%2Ffloating-calculations-info%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB6%20has%20the%20value%26nbsp%3B0.99999999999999989.%3C%2FP%3E%3CP%3EB7%20has%20the%20value%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513101%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513101%22%20slang%3D%22en-US%22%3E%3CP%3EEnlightening%20!.%20Thank%20you%20-ERR%3AREF-NOT-FOUND-%40Detlef%20Lewin%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513166%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513166%22%20slang%3D%22en-US%22%3E%3CP%3E%26amp%3Bnbsp%3Bif%20you%20never%20enter%20anything%20beyond%20the%202nd%20decimal%2C%20use%20round(....%2C%202)%26amp%3Bnbsp%3B%3C%2FP%3E%3CSCX-1%3E%3C%2FSCX-1%3E%3CP%3E%3CBR%20%2F%3E%26amp%3Bnbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513311%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513311%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F724081%22%20target%3D%22_blank%22%3E%40BilloCL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELooks%20like%20floating%20point%20error%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%3C%2FA%3E%26nbsp%3Bin%20custom%20format%20handling%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513348%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513348%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494140%22%20target%3D%22_blank%22%3E%40wsantos%3C%2FA%3E%26nbsp%3B.%20Rounding%20works%2C%20either%20in%20each%20factor%20or%20applied%20to%20the%20whole%20sum.%20Excel's%20funky%20floating%20point%20arithmetic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513349%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513349%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20was.%20Applying%20ROUND()%20to%20the%20adding%20factors%20worked.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513350%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513350%22%20slang%3D%22en-US%22%3E%3CP%3Ea%20variant%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494140%22%20target%3D%22_blank%22%3E%40wsantos%3C%2FA%3E%26nbsp%3Banswer%20just%20change%20the%20conditional%20formatting%20to%20be%20%26lt%3B0.9999%20and%20%26gt%3B1.0001%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516151%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Number%20Format%20BUG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20wrote%3A%20%60%60B6%20has%20the%20value%200.99999999999999989.%20B7%20has%20the%20value%201%60%60%2C%20referring%20to%20the%2017-digit%20decimal%20representation%20in%20the%20XML%20file%2C%20which%20is%20how%20%22xlsx%22%20and%20%22xlsm%22%20files%20are%20stored%2C%20in%20contrast%20to%20the%2015%20digits%20that%20Excel%20displays%2C%20an%20arbitrary%20limit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20in%20another%20response%20a%20year%20ago%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20referred%20to%20the%20explanation%20in%20%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2F17-or-15-digits-of-precision%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E17-or-15-digits-of-precision%3C%2FA%3E%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20clarify%3A%26nbsp%3B%20it%20is%20just%20as%20misleading%20to%20say%20that%20Excel%20%22stores%22%20values%20with%2017-digit%20precision%2C%20as%20it%20is%20to%20say%20that%20it%20%22stores%22%20values%20with%2015-digit%20precision.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20does%20represent%20numeric%20values%20with%20up%20to%2017%20significant%20decimal%20digits%20in%20XML.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20that%20is%20because%20the%20IEEE%20794%20standard%20states%20that%2017%20significant%20digits%20(not%2015)%20is%20necessary%20and%20sufficient%20to%20convert%20all%20binary%20values%20between%20binary%20and%20decimal%20representations%20with%20no%20loss%20of%20precision.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%2C%20the%2017-digit%20representation%20is%20still%20only%20an%20approximation%20of%20the%20binary%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20actually%20stores%20the%20exact%2064-bit%20binary%20floating-point%20value%20into%20computer%20memory%20when%20the%20application%20is%20running.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20exact%20decimal%20representation%20of%20the%20binary%20value%20can%20have%20many%20more%20significant%20digits%20than%20even%2017%20digits%20--%20up%20to%201022%20fractional%20digits%20with%20714%20significant%20digits%20for%20the%20smallest%20positive%20binary%20value%2C%20which%20is%202%5E-1022%2C%20about%26nbsp%3B2.2250738585072013E-308%20(in%20VBA%2C%20because%20VBA%20does%20not%20truncate%20decimal%20data%20input%20after%2015%20significant%20digits%2C%20as%20Excel%20does).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20demonstrate%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20displays%20(16%20dp)%3CBR%20%2F%3EB6%3A%20%3D0.85%2B0.06%2B0.09%20%201.0000000000000000%3CBR%20%2F%3EB8%3A%20%3D0.85%2B0.06%20%20%20%20%20%20%200.9100000000000000%3CBR%20%2F%3EB9%3A%20%3DB6-B8%20%20%20%20%20%20%20%20%20%20%200.0900000000000000%3C%2FPRE%3E%3CPRE%3E%20%20%20%20XML%20representation%20%20%20exact%20decimal%20representation%20of%20binary%20value%3CBR%20%2F%3EB6%3A%200.99999999999999989%20%200.999999999999999%2C88897769753748434595763683319091796875%3CBR%20%2F%3EB8%3A%200.90999999999999992%20%200.909999999999999%2C9200639422269887290894985198974609375%3CBR%20%2F%3E%20%20%20%20-------------------%3CBR%20%2F%3EB9%3A%200.08999999999999997%20%20expected%20based%20on%20XML%20appearance%20of%20B6%20and%20B8%3B%20but%20wrong(!)%3C%2FPRE%3E%3CPRE%3Ecorrect%20result%3A%3CBR%20%2F%3E%20%20%20%20XML%20representation%20%20%20%20exact%20decimal%20representation%20of%20binary%20value%3CBR%20%2F%3EB9%3A%200.089999999999999969%20%200.0899999999999999%2C6891375531049561686813831329345703125%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(For%20the%20exact%20decimal%20representations%2C%20I%20use%20period%20for%20the%20decimal%20point%20and%20comma%20to%20demarcate%20the%20first%2015%20significant%20digits.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Excel%20truly%20%22stored%22%20only%2017%20decimal%20(significant)%20digits%2C%20we%20would%20expect%20that%20B9%20is%20stored%20as%200.08999999999999997%20in%20XML.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%2C%20we%20see%200.089999999999999969%20in%20XML.%20(Actually%2C%208.9999999999999969E-2.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20is%20demonstrated%20by%20the%20exact%20decimal%20representations%20of%20the%20binary%20values%2C%20on%20the%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Caveat%3A%20The%20difference%20between%20approximate%20decimal%20and%20exact%20binary%20representations%20is%20not%20always%20so%20obvious%2C%20due%20to%20the%20%22normalization%22%20of%2064-bit%20binary%20floating-point%20and%20due%20to%20the%20fact%20that%20Intel-compatible%20CPUs%20actually%20use%20non-normalized%2080-bit%20binary%20floating-point%20for%20binary%20arithmetic.)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

@BilloCL 

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

Enlightening !. Thank you @Detlef Lewin 

Highlighted
<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

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

Highlighted

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

Thank you for your reply @Sergei Baklan 

Highlighted

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

Highlighted

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