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 t...

FreakBug.xlsx9 KB

excel

office 365

Detlef_Lewin
Jul 09, 2020
BilloCL
Congratulations. You just found out about the floating point error:
https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
https://docs.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info

B6 has the value 0.99999999999999989.
B7 has the value 1.

wsantos

Brass Contributor

Jul 09, 2020

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

BilloCL

Copper Contributor

Jul 09, 2020

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

Forum Discussion

Conditional Number Format BUG

Resources