Conditional Formatting using Formula Option

[Hazeyt]

I have a list of Departments with Headcount figures (B8 to B19) then percentages from April to November in other columns (G8 to S19).

I have already added conditional formatting to show all departments with 9% or more and only departments who have a headcount greater than 20.

The formula I am using is =AND(G8>=9%,$B8>20) this fills the cells Red.

I now want to add another rule to change any percentages between 6 and 9 to fill the cell Amber. Still only for departments that have a headcount greater than 20.

Does anyone know the formula to add to this?

Thank you

 

[6StringJazzer]

"Between" inclusive, or "between" exclusive"? Here is exclusive, change to suit.

=AND($G8>6%,$G8<9%, $B8>20)

 

[Hazeyt]

"Between" inclusive, or "between" exclusive"? Here is exclusive, change to suit.

=AND($G8>6%,$G8<9%, $B8>20)

Thank you

Unfortunately this isn't working. Is there another formula I could use?

Thanks

 

[6StringJazzer]

Please show sample data and expected results. Here are the results for your original formula for red plus my new formula for amber.

$scratch.xlsm
	B	C	D	E	F	G
8	34					8%
9	40					4%
10	33					8%
11	21					12%
12	24					11%
13	15					8%
14	20					11%
15	28					3%
16	40					3%
17	50					6%
18	43					8%
19	22					3%
Departments

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:G19	Expression	=AND($G8>6%,$G8<9%, $B8>20)	text	NO
B8:G19	Expression	=AND($G8>=9%,$B8>20)	text	NO

 

[Hazeyt]

Thank you so much, I now have it working.