Conditional Formatting a RAG system based on percentage

[NickM88]

Hi guys, New on the forum and new to Excel, but have used this forum for workarounds before, but need some help!

I want to use a conditional format for RAG (Red, Amber, Green) for column F, based on what percentage the number is for column D.

EG
Column D Column F
Sales Add Ons
2 1
4 3
6 2

Want to conditional format F columns so it is percentage based on sales, if that makes sense? :

<50% Red
50-69% Amber
>70% Green

Any suggestions?

 

[Oaktree]

Welcome to the board.

If I understand your question correctly, you want to apply conditional formatting to column F according to cutoff points for the value of F[row]/D[row].

Assuming that's right
(1) first click on cell F2 to select it
(2) Go to Home --> Conditional Formatting --> Manage Rules
(3) Click New Rule
(4) In the top "Select Rule Type" box, select "Use a formula to determine which cells to format"
(5) In the middle box for "Format values where this formula is true", enter =(F2/D2)<0.5.
(6) Click "Format", go to "Fill", and find the red color you like.
(7) Click OK once to confirm the fill color and click OK again to confirm your first rule
(8) Repeat steps 3 through 7, changing the formula in step 5 to =AND((F2/D2)>=0.5,(F2/D2)<0.7) for the yellow one and =(F2/D2)>=0.7 for the green one. Also change the color in step 6 accordingly.
(9) Copy cell F2
(10) Selecct F3:F[last row] and go to home --> paste --> formatting

 

[NickM88]

Welcome to the board.

If I understand your question correctly, you want to apply conditional formatting to column F according to cutoff points for the value of F[row]/D[row].

Assuming that's right
(1) first click on cell F2 to select it
(2) Go to Home --> Conditional Formatting --> Manage Rules
(3) Click New Rule
(4) In the top "Select Rule Type" box, select "Use a formula to determine which cells to format"
(5) In the middle box for "Format values where this formula is true", enter =(F2/D2)<0.5.
(6) Click "Format", go to "Fill", and find the red color you like.
(7) Click OK once to confirm the fill color and click OK again to confirm your first rule
(8) Repeat steps 3 through 7, changing the formula in step 5 to =AND((F2/D2)>=0.5,(F2/D2)<0.7) for the yellow one and =(F2/D2)>=0.7 for the green one. Also change the color in step 6 accordingly.
(9) Copy cell F2
(10) Selecct F3:F[last row] and go to home --> paste --> formatting

Thanks for responding,

Tried this, and it works for red and green, but the yellow/amber one just turns white instead of the colour I picked?

 

[NickM88]

Is it the order of the rules??

 

[Oaktree]

Is it the order of the rules??

Only if the conditions overlap.

Try copying the formula you're using for a cell you're expecting to be yellow/amber one and putting it into any unused cell. Does it return TRUE as you'd expect it to?

 

[NickM88]

Fixed! Thanks so much!