Jun 05 2020 11:45 AM - edited Jun 05 2020 11:46 AM
Hello, I hope you are all well.
I'm having an issue with a worksheet and the Conditional Formatting I set up. I have the formatting set to turn the cell: 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.
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. I have attached a screenshot.
If anyone could assist and let me know what I am doing wrong, I'd greatly appreciate it.
Jun 05 2020 11:55 AM
Jun 05 2020 11:58 AM
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
Jun 05 2020 12:18 PM - edited Jun 05 2020 12:23 PM
@Sergei Baklan 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 <>"" )
Jun 05 2020 12:46 PM
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
Jun 08 2020 06:38 AM
@peteryac60 Thank you very much! Yes...that worked. I wasn't aware that the conditions had to be in an order. You taught me something very valuable! I very much appreciate your response. :)
Jun 08 2020 06:41 AM - edited Jun 08 2020 06:43 AM
I thank you very much for replying. I was able to get it to work properly with Peter's suggestion.@Sergei Baklan
Jun 08 2020 06:42 AM
Jun 08 2020 06:44 AM
Jun 08 2020 06:51 AM
You are welcome.
Note also the comments left by @Sergei Baklan especially that text is 'greater' than numbers - I should have mentioned this.
anyway good luck!
Pete