Conditional Formatting using 3 colours for percentage ranges.

[Anga73]

Hi, I have searched but not able to find what I'm after.

I am trying to return 3 different colours for percentage ranges in excel.

I can get two colours to work - but not the 3rd.

This is what I'm trying:

IF$F$76>=95% (Green)
IF$F$76>=85% (Yellow)
IF$F$76<85% (Red)

I can get Red and Green to work, or Red and Yellow, but not all 3.

Anyone know what I'm doing wrong?

 

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$F$76>=95

Repeat 3 and 4 for the other 2 rules

 

[FDibbins]

I just noticed that your rules seem a little mixed up?

IF$F$76>=95 (Green)
IF$>=85 (Yellow)
IF<85 (Red)

Unless that is a typo, your 2nd rule is pretty much redundant. If a value is >= 85, the 1st rule will always kick in, while the 2nd rule will be ignored

 

[Anga73]

Yeah that is my problem - the second rule is being ignored - I want anything under 85% to show as red, anything over 95% to be green and any between 86 and 94% to be yellow.

 

[FDibbins]

Then you only need 2 rules...

=$F$76<.85 Red
=$F$76>.95 Green

Then set the default color of the cell to yellow

 

[Anga73]

Totally makes sense and was my next option - thanks for your help and so quick, really appreciate it.

 

[mfexcel]

Mind the sequence of the conditional formatting.
the 1st condition should be >=0.95
the 2nd condition: >=0.85
the 3rd condition: <0.85

Yeah that is my problem - the second rule is being ignored - I want anything under 85% to show as red, anything over 95% to be green and any between 86 and 94% to be yellow.

 

[Anga73]

Mind the sequence of the conditional formatting.
the 1st condition should be >=0.95
the 2nd condition: >=0.85
the 3rd condition: <0.85

OMG - that works Thank you, thank you - I didn't realise the sequence played a part!

 

[mfexcel]

You are welcome. Glad to help!

OMG - that works Thank you, thank you - I didn't realise the sequence played a part!