Conditional Formatting Formula

[Pavan Kumar Sistla]

Dear All,

Could you please guide me how to highlight Red, Amber, Green (RAG) using conditional formatting based on the cell value. The criteria is below:

a) Plus or Minus 10% => Red
b) Plus or Minus 5% => Green
c) Plus or Minus 5 to 10% => Amber

Thank You and Regards,
Pavan.

 

[GraH - Guido]

Can you upload a file please?
Based on understanding of the above, rules based on formulas if cells are in column B.
amber =AND(ABS($B1)>5%,ABS($B1)<10%)
green = ABS($B1)=5%
red = ABS($B1)=10%

 

[Pavan Kumar Sistla]

Kindly note that if the value is in cell A2, and tried the below formula which is not working.

=IF(OR(A2<5%,A2>-5%),"Green",IF(OR(A2<9%,A2>-9%),"Amber","Red"))

Please correct and make it work.

Thank You and Regards,
Pavan.

 

[Pavan Kumar Sistla]

Can you upload a file please?
Based on understanding of the above, rules based on formulas if cells are in column B.
amber =AND(ABS($B1)>5%,ABS($B1)<10%)
green = ABS($B1)=5%
red = ABS($B1)=10%

Thank You GraH. However, please note that if the value is below + or - 5 also, it should turn Green.

 

[GraH - Guido]

Pavan, Did you check how I did it in the uploaded file?
I use ABS(A2)=5% => So it does not matter if it is MIN/PLUS.
And I defined 3 rules: one for each colour.
FYI: CF works if the rule returns TRUE.