Conditional Formatting Multiple Criteria

[dch5876]

I want to apply conditional formatting to column M based on the wavelength in column G. I do not want to compare it to the actual wavelength but a criteria based on the different wavelength. So 1310 would have have max value of .4500, 1550 would have have a max value of .2500 and 1625 would have a max value of .3500. I want any value in column M that exceed the max value of the wavelength of column G to turn red and any value that is equal to the max value or below turn green.

I was thinking an OR statement but cant find a configuration that works.

=OR(G2=1550 =< 0.2500, C2=1625 =< 0.3500) Green
=OR(G2=1550 > 0.2500, C2=1625 < 0.3500) Red

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]G[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Wavelength[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1310[/TD]
[TD]0.2011[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1550[/TD]
[TD]0.1916[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1625[/TD]
[TD]0.1984[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

If you want an OR statement, consider the formula in H2.

Book1
	G	H	I	J	K	L	M	N	O	P
1	Wavelength						Status		Wavelength	Max
2	1310	TRUE	FALSE	TRUE			0.2011		1310	0.45
3	1550	TRUE	FALSE	TRUE			0.1916		1550	0.25
4	1625	TRUE	FALSE	TRUE			0.1984		1625	0.35
Sheet3

Cell Formulas
Range	Formula
H2	=OR(AND(G2=1310,M2<0.45),AND(G2=1550,M2<0.25),AND(G2=1625,M2<0.35))
I2	=NOT(OR(AND(G2=1310,M2<0.45),AND(G2=1550,M2<0.25),AND(G2=1625,M2<0.35)))
J2	=M2<=VLOOKUP(G2,$O$2:$P$4,2)

To get the opposite function for your red condition, you can just wrap that formula in a NOT (I2). It will also turn red if the value in G2 isn't 1310, 1550, or 1625. However, I'd recommend building a table with your values, like I did in O1:P4, then the formula you need would be much simpler (J2).

Hope this helps!

 

[dch5876]

Eric,
Appreciate the response, but was hoping to take care of this using conditional formatting. I originally wrote an IFS statement in column O and applied conditional formatting to the cells. (See Below) But wanted to try and not have hidden columns as others will be pasting most of the contents into the worksheet.


=IFS(H2=1310, 0.41,H2=1550, 0.251,H2=1625, 0.351)
Conditional Formatting
Cell Value<$O2 Green
Cell Value>=$O2 Red

 

[Eric W]

All 3 of the formulas are designed to work in Conditional Formatting. Select range G2:G4 (or whatever your whole range is), click Conditional Formatting > New Rule > Use a formula > and enter one of the formulas. I only showed them on the sheet to show the results, but you don't need to use a helper column.

Incidentally, I don't use IFS much, but you can use this as your green CF formula too:

=IFS(G2=1310,M2<0.45,G2=1550,M2<0.25,G2=1625,M2<0.35)

 

[dch5876]

Thanks Eric! Worked like a charm. I also like the added benefit of it calling out wavelength.