Conditional formatting RAG status colouring

[bls2024]

Hi

I'm setting up a dashboard that has a target column (column C) and an actual column (column D). I want to have an auto formatted RAG status in column E. I've provided 3 lines of data in the hope that someone can tell me what steps I need to take and formula to write for each rule to get the colours to auto populate in column E please. I tried following steps on google and managed to write rules but they didn't change the colour

Target Actual RAG
Data Line 1 100% 75%
Data Line 2 95% 89%
Data Line 3 100% 99%

Rules 95% and above is green, 85% to 95% is amber, below 85% is red

I hope someone can help

Thanks

 

[etaf]

conditional formatting would do this for you
3 rules , one for each colour
BUT
is this % of Actual v target OR just the value in actual

in your example
Data line 2
95% is target
89% is actual

89/95 = 93.86% ???? so amber ?

OR is it just 89% - still amber ?

 

[etaf]

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
E2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$D2>=0.95 OR =$C2/$D2>=0.95

Format [Number, Font, Border, Fill] - FORMAT GREEN
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other 2 rules -
=$D2>=0.85 OR =$C2/$D2>=0.85
FORMAT AMBER

=AND($D2<>"",$D2<0.85 ) OR =AND($C2<> "" , $C2/$D2<0.85)
FORMAT RED

AND put in that order and USE STOP IF TRUE

Assuming just looking at column D

Book1
	A	B	C	D	E	F
1
2			100%	75%
3			95%	85%
4			100%	99%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E4	Expression	=AND($D2<>"",$D2<0.85)	text	YES
E2:E4	Expression	=$D2>=0.95	text	YES
E2:E4	Expression	=$D2>=0.85	text	YES

 

[bls2024]

conditional formatting would do this for you
3 rules , one for each colour
BUT
is this % of Actual v target OR just the value in actual

in your example
Data line 2
95% is target
89% is actual

89/95 = 93.86% ???? so amber ?

OR is it just 89% - still amber ?

 

[bls2024]

Thanks for replying. It is the former 94.86% so amber. Thanks

 

[bls2024]

conditional formatting would do this for you
3 rules , one for each colour
BUT
is this % of Actual v target OR just the value in actual

in your example
Data line 2
95% is target
89% is actual

89/95 = 93.86% ???? so amber ?

OR is it just 89% - still amber ?

 

[etaf]

OK, then 2nd formula

Highlight applicable range >>
E2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$D2/$C2>=0.95

Format [Number, Font, Border, Fill] - FORMAT GREEN
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other 2 rules -
=$D2/$C2>=0.85
FORMAT AMBER

=AND($C2<> "" , $D2/$C2<0.85)
FORMAT RED

AND put in that order and USE STOP IF TRUE

 

[bls2024]

It is 93% amber thanks

 

[etaf]

sorry I think i misunderstand the request - sorry about that and not explained myself -

you target is 95% and the actual is 89%
you see that as 93% achieved ???

can you explain further

I thought actual / target 89/95 =93.6%
Actual is in column D and target is in column C D2/c2
so that as you say is amber

hence post 7

Book1
	A	B	C	D	E	F
1
2			100%	75%		75.00000000000000%
3			95%	89%		93.68421052631580%
4			100%	99%		99.00000000000000%
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=D2/C2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E12	Expression	=$D2/$C2>=0.95	text	NO
E2:E13	Expression	=$D2/$C2>=0.85	text	NO
E2:E12	Expression	=AND($D2<> "", $D2/$C2<0.85)	text	NO

 

[bls2024]

I'll try that later and confirm if it worked. thank you

OK, then 2nd formula

Highlight applicable range >>
E2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$D2/$C2>=0.95

Format [Number, Font, Border, Fill] - FORMAT GREEN
choose the format you would like to apply when the condition is true
OK >> OK

repeat for other 2 rules -
=$D2/$C2>=0.85
FORMAT AMBER

=AND($C2<> "" , $D2/$C2<0.85)
FORMAT RED

AND put in that order and USE STOP IF TRUE

First method definitely didn't work it changed some of the cells that should have been green or red to amber and left the bottom two rows white - despite me highlighting all rows in the column. I'll try the 2nd method later today . Hopefully it will work
. Thank you