Conditional Formatting w/ Formulas

[Austin Lang]

Hey all,

I feel like this is a simple one but I am having a brain fart about it..

I have a document that has these separate conditional formatting rules.. How do I go about combining these into one? I attempted to add OR but I am falling short somewhere.

=AND($A5="Circuit 1",$AB5<1.5)
=AND($A5="Circuit 2",$AB5<1.5)
=AND($A5="Circuit 3",$AB5<1.5)

Thanks in advance.

 

[kevin9999]

How about...

CF.xlsx
	A																					V	W
4
5	Circuit 1																					8
6	Circuit 6																					8
7
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V5:V6	Expression	=OR(AND($V5>4,$A5<>"Circuit 1",$A5<>"Circuit 2",$A5<>"Circuit 3"),(AND($V5>15,OR($A5="Circuit 1",$A5="Circuit 2",$A5="Circuit 3"))))	text	NO

 

[bebo021999]

Again:

=$V5>IF(OR($A5="Circuit 1",$A5="Circuit 3",$A5="Circuit 3"),15,4)

or even:

=$V5>IF(RIGHT($A5,2)-4<0,15,4)

 

[kevin9999]

Bigger sample data

CF.xlsx
	A																					V	W
4
5	Circuit 1																					8
6	Circuit 2																					15
7	Circuit 3																					16
8	Circuit 4																					4
9	Circuit 5																					5
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V5:V9	Expression	=OR(AND($V5>4,$A5<>"Circuit 1",$A5<>"Circuit 2",$A5<>"Circuit 3"),(AND($V5>15,OR($A5="Circuit 1",$A5="Circuit 2",$A5="Circuit 3"))))	text	NO

 

[Austin Lang]

Again:

=$V5>IF(OR($A5="Circuit 1",$A5="Circuit 3",$A5="Circuit 3"),15,4)

or even:

=$V5>IF(RIGHT($A5,2)-4<0,15,4)

The first formula does work. Thank you for that! I think big, so what about if I want to say...

Circuit 1-3: >15
Circuit 4-6: >20
Circuit 7-50: >4

Can we easily adapt the formula to handle that? I promise this is the last one. Haha

 

[bebo021999]

If last 2 letters of A5 is alway from 1 to 50, try:

=$V5>LOOKUP(RIGHT(A5,2)+0,{1,4,7},{15,20,4})

 

[Austin Lang]

The circuit names all have unique names. I had changed them to Circuit 1 and so on for the question.

 

[bebo021999]

So, what their names actually are?
Not "Circuit ##"?

 

[Austin Lang]

So, what their names actually are?
Not "Circuit ##"?

Correct. They are all different. I do have some with numbers at the end, such as "Tank 15". I also have equipment like "Fridge Thermometer".

 

[bebo021999]

You should create a lookup table with 2 columns, one is circuit name and other is value limit
Tank 15 ___4
Fridge Ther ___15
.....

 

[Austin Lang]

If I were do that, what would you suggest the formula be for the conditional formatting?