Conditional Formatting using 3 colours for percentage ranges.

Anga73

New Member
Joined
Sep 7, 2014
Messages
4
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?
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Then you only need 2 rules...

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

Then set the default color of the cell to yellow
 
Upvote 0
Totally makes sense and was my next option - thanks for your help and so quick, really appreciate it.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top