3-color conditional formatting using formula

[ismoo1]

Hi All!

3-color conditional formatting using formula

I have an issue how to create 3-color formatting based on formula relying onto few cells values, but cannot get it working nor found help from colleagues/google


I enter formulas via "conditional formatting" -> "color scales! -> "format all cells based on values" -> "3-color scale"
- As type I select "formula"

It seems that formula's has no whatsover effect regardless whatever I enter, also wonder as it approves only fixed references how could it work for several rows?

Col1 Col2
2 1
3 2
4 3

Green, if Col1>3 ot Col2 >1
Red if Col1<3 and Col2<2
Yellow if Col1<3 and Col2>1

Ismo

 

[Special-K99]

What results are you expecting?
Your first row will have no effect since none of the three formulas are TRUE. (GREEN: 2 is not > 3, 1 is not > 1, RED: 2 is less than 3 but 2 is not less than 1, YELLOW: 2 is less than 3 but 1 is not > 1 hence no effect).
Your second row will be green
Your third row will be green

You typed "ot" in one formula, I presume thats meant to be OR ?

Try this

Select A1:B3

Conditional Formatting
New Rule
Use a formula to determine...
3 formulas needed

=OR(A1 > 3,B1 > 1)
format as green

=AND(A1 < 3,B1 < 2)
format as red

=AND(A1 < 3,B1 > 1)
format as yellow



Format as required

 

[ismoo1]

Thanks SpecialK99!

Sorry for my poor question statement, but picked up the key point from your answer being able to have several nested formulas in sequence and was able to achieve desired output after some iteration.
Excel Formula option in 3-color different limits was really misleading