I'm not sure why Conditional Formatting isn't working correctly

Question

Hello,&nbsp; I hope you are all well.&nbsp;&nbsp;I'm having an issue with a worksheet and the Conditional Formatting I set up.&nbsp; I have the formatting set to turn the cell:&nbsp; Red if the value in the cell is less than 85%, Green if the value in the cell is 85% and above, and Blue, if the value in the cell is blank.&nbsp;I have tried clearing the entire cell, and redoing everything and I still have the same result.What is happening is when the cell is blank it remains green instead of blue.&nbsp; I have attached a screenshot.&nbsp;If anyone could assist and let me know what I am doing wrong, I'd greatly appreciate it.&nbsp;&nbsp;

peteryac60 · Answer

HiI’m not able to look at your attachment at present but I suspect that you have the conditions in the wrong order. If you put the blank check first ( you can move it up) and then check the box “stop if true”. This will then not apply the other checks. You can then have the other 2 formats applied in any order.Hope this helpsPetet

sergeibaklan · Answer

Michael1105&nbsp;
That's since value of the blank cell is equivalent to zero, 0.85 is greater than zero, green condition triggers the color and next rule is not checked. You shall change (&gt;0.85 and &lt;&gt;0 ) for this rule

mtarler · Answer

SergeiBaklan&nbsp; well this is a first for me.&nbsp; Sergei, I did some testing and ""&lt;&gt;0&nbsp; and in fact it appears any text is evaluating &gt; any number.&nbsp; For example "1"&gt;999999 results TRUE&nbsp; (but --"1"&gt;9 evaluates FALSE as expected)so&nbsp;@MichaelAntonelli&nbsp; change the rule to be (&gt;0.85 and &lt;&gt;"" )

sergeibaklan · Answer

mtarler&nbsp;
Yes, exactly, any text is always "greater" than any number. But I don't see why do we shall to mix numbers and texts together.
&nbsp;
First variant, as&nbsp;peteryac60&nbsp; suggested, is to move checking of blank cell on first position. "Stop if True" not necessary

This one is simplest and easiest. With only one but. In 95% of cases it works perfectly, but sometimes Excel re-order sequence of rules automatically. These are relatively complex cases, nonetheless, where possible it's better be independent of rules sequence. Like this:

with not complex formulas
=(G2&lt;0.85)*(G2&lt;&gt;0)
=(G2&gt;=0.85)*(G2&lt;&gt;0)
=G2=0

michael1105 · Answer

peteryac60&nbsp;Thank you very much!&nbsp; Yes...that worked.&nbsp; I wasn't aware that the conditions had to be in an order.&nbsp; You taught me something very valuable!&nbsp; I very much appreciate your response. 🙂

Forum Discussion

I'm not sure why Conditional Formatting isn't working correctly

9 Replies

Resources