Hi all.
My goal is to apply conditional formatting on a specific cell based on the values in that cell and another cell.
Example: apply formatting to cell $E$2.
If value of D2 is <120 and value of cell E2 ,<80 then apply STYLE(“BP-Green”).
If value of D2 is between 120 – 129 and value of cell E2 <80 then apply STYLE(“BP-Yellow”).
If value of D2 is between 130 – 139 or value of cell E2 is between 80 – 89 the apply STYLE(“BP-Orange”).
If value of D2 between 140 - 179 or if value of cell E2 is =>90 apply STYLE(“BP-BurntOrange”).
If value of D2 is =>180 and/or value of cell E2 is =>120 apply STYLE(“Crisis”).
The following image may help. I want the cell background to correspond to the Image.
I am able to use the built in conditional formatting dialog for the first condition,see screen-shot below, but I don’t really know how to construct formula for the additional conditions.
I am assuming if this can be done it will be accomplished by using nested “IF” and “ElseIF” statement.
I have tried to begin the formula with “IF(AND but not getting desired result.
As I understand it Boolean expressions combined with AND only return the value True if both expressions evaluate to True:
True AND True returns True; for all other combinations the result is False.
My assumption is that the formula below would return False if cell E11 value was greater 80, but it seems that if if cell D11 is between 120 - 129 the formula returns True because the BP-Yellow style is applied.
=IF(AND($E$11<=80,OR($D$11=120,$D$11=121,$D$11=122,$D$11=123,$D$11=124,$D$11=125,$D$11=126,$D$11=127,$D$11=128,$D$11=129),STYLE(“BP-Yellow”),STYLE(“Default”)))”