Setting conditional formatting to show a lower and upper limit in the same cell

Livaya · Sep 26, 2016

I am using the traffic light conditional formatting to show the results of a calculation from another cell. I want the traffic light to be:
Green for any result that is between 0.95 - 1.1,
YELLOW for any result between 0.81 - 0.94 and also 1.1-1.24,
RED for any result that is less than or equal to 0.8 and also greater than or equal to 1.25.

This is the formula I have in the cell =IF(ISERROR(E14/E13)," ",(E14/E13)). I was able to set the traffic light up for the lower limits for each color but not for both lower and upper limit in the same cell. Someone suggested using Logical OR function, but I have never used this and I don't understand it.

Does anyone know how to do this? Please help.

navic · Sep 26, 2016

Livaya said:
Does anyone know how to do this? Please help.

In Conditional Formatting try next formulas

GREEN

Code:

=AND(E19>0,95;E19<1,1)

YELLOW

Code:

=AND(E19>=1,1;E19<=1,24)

and

Code:

=AND(E19>=0,81;E19<=0,94)

RED

Code:

=OR(E19<=0,8;E19>=1,25)

Setting conditional formatting to show a lower and upper limit in the same cell

Livaya

New member

navic

New member

Attachments