complete novice seeking help with a conditional formatting RAG status colouring question. Help please

[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