Conditional formatting with multiple criteria and different cells

Question

I have cell J1, which contains a number, determined by a formula. I have cell J2, which contains a dropdown obtained by data validation: CES,CES1,CES2,CES3. If J2 = CES, and J1 > 20, I want J1 to be green, (goal was met). If J2 = CES and J1 < 20, J1 should be red. If J2 = CES1, CES2, or CES3, AND if J1 > 35, J1 should be green. Otherwise, J1 should be red. In other words, for CES, the goal is 20, and for CES1/2/3, the goal is 35. How can I accomplish this? TIA.

Answer 1

You can take advantage of the fact that J2 has data validation to simplify the formula significantly. Set J1 to red by default, and then use conditional formatting to turn it green if J2 is CES and J1 is greater than 20, or if J1 is greater than 35 (regardless of what J2 is, since we know the only other options will be CES1/2/3).

(In your example you have defined behavior for greater than and less than 20/35 but not what to do if it equals 20/35. Since you described it as the goal number I've assumed that when they're equal J1 should be green. If my assumption is incorrect simply change "J1>=20" and "J1>=35" to "J1>20" and "J1>35" respectively.)

Here is the formula for the rule that turns the cell green:

=OR(AND(EQ("CES",J2), J1>=20), J1>=35)

Alternatively, if you don't want to rely on data validation for some reason, like if there's another option that could appear in J2 that shouldn't turn J1 green regardless of number, you can use this longer formula instead:

=OR(AND(EQ("CES",J2), J1>=20), AND(OR(EQ("CES1",J2), EQ("CES2",J2), EQ("CES3",J2)), J1>=35))

(This does the same thing, only now if J1 is greater than 35 it will check that J2 contains either CES1, CES2 or CES3.)

If you do not want to set the color to red by default, and would like a second rule to turn it red, then placing the entire formula (after the equals sign) inside a NOT() function will provide you the formula for the red rule.