Conditional format within %

[nparsons75]

Hi,

I need to setup conditional formatting to show a different colour based on how close to a target we are. In the table below for example,

If we are less than 70% of the target the colour is set to red.
Between 70% and 90% the colour is amber.
90% and above is green.

The colour scale is an example and the % ranges may need to be altered but hopefully the table below will show what I am hoping for.

Thanks in advance.

Book1
	A	B	C	D	E	F
1	Target	2000	2000	2000	2000	2000
2	Actual	1200	1400	1600	1800	2000
3	%	60%	70%	80%	90%	100%
Sheet3

Cell Formulas
Range	Formula
B3:F3	B3	=B2/B1

 

[etaf]

Setup three rules

using
=B3<=0.7 for Red
=B3<=0.9 for Amber
=B3<=1 for Green

Using STOP If TRUE

Book3
	A	B	C	D	E	F
1	Target	2000	2000	2000	2000	2000
2	Actual	1200	1400	1600	1800	2000
3	%	60%	70%	80%	90%	100%
Sheet1

Cell Formulas
Range	Formula
B3:F3	B3	=B2/B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:F3	Expression	=B3<=0.7	text	YES
B3:F3	Expression	=B3<=0.9	text	YES
B3:F3	Expression	=B3<=1	text	YES

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

Highlight applicable range >>

B3:F3


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:

RULE 1

=B3<=0.7


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

Repeat for other 2 rules

 

[nparsons75]

Setup three rules

using
=B3<=0.7 for Red
=B3<=0.9 for Amber
=B3<=1 for Green

Using STOP If TRUE

Book3
	A	B	C	D	E	F
1	Target	2000	2000	2000	2000	2000
2	Actual	1200	1400	1600	1800	2000
3	%	60%	70%	80%	90%	100%
Sheet1

Cell Formulas
Range	Formula
B3:F3	B3	=B2/B1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:F3	Expression	=B3<=0.7	text	YES
B3:F3	Expression	=B3<=0.9	text	YES
B3:F3	Expression	=B3<=1	text	YES

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

Highlight applicable range >>

B3:F3


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:

RULE 1

=B3<=0.7


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

Repeat for other 2 rules

 

[nparsons75]

Perfect, thank you for the support....

 

[etaf]

you are welcome

 

[nparsons75]

Just stumbled on a problem...

I also want everything over and above zero (0) to be green?

 

[nparsons75]

Oops, ignore, think I have managed it, just added: =Y11>=1

 

[etaf]

above zero - thats red as below 70%
Y11 ???

Not sure what you are after, BUT you say you managed to include
=Y11>=1 so thats greater or equal to 1 not zero

Anyway , come back if needed