Forum Discussion
I'm not sure why Conditional Formatting isn't working correctly
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 (>0.85 and <>0 ) for this rule
- Thank you very much for responding. I tried the suggestion from Peter and it worked.
I thank you very much for replying. I was able to get it to work properly with Peter's suggestion.SergeiBaklan
SergeiBaklan well this is a first for me. Sergei, I did some testing and ""<>0 and in fact it appears any text is evaluating > any number. For example "1">999999 results TRUE (but --"1">9 evaluates FALSE as expected)
so
@MichaelAntonelli change the rule to be (>0.85 and <>"" )
- Thank you for replying. I did try Peter's suggestion and it seemed work fix the issue. Much appreciated 🙂
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.
First variant, as peteryac60 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<0.85)*(G2<>0) =(G2>=0.85)*(G2<>0) =G2=0
