Conditional Formatting

[Ginger John]

Hi,
I have a in Cells D2:D40 percentages which I would like to format Highest RED, 2nd Highest Green, and 3rd Highest Yellow.
In the Cells there are gaps in the data as follows

18%
22%
5%
13%

16%
23%
55
14%
I wan to format 22% and 23% RED, 18% and 16% Green, And 13% and 14% Yellow, but can this be done with the gaps inbetween the Data.

Many thanks
Ginger John

 

[Marcelo Branco]

Hi,

Welcome to the forum!

If i understood correctly, you want

1st and 2nd greatest ---> RED
3rd and 4th ---> GREEN
5th and 6th ---> YELLOW

Am i right?

M.
ps: there is a 55 in the middle of your data. Is it a typo?

 

[Ginger John]

Hi Marcelo.
What i am after is the Percentage cells to colour like this.
The problem I have is if I try to Conditional format the cells it doesn't work properly because of the space between 13% and 16%.
Many Thanks
John

18% = GREEN
22% = RED
5%
13% = YELLOW

16% = GREEN
23% = RED
5%
14% = YELLOW

 

[Ingolf]

Hi,
You have to applay CF for every range, something like this:
=RANK(B1;$B$1:$B$4)=1 ---> Red
=RANK(B1;$B$1:$B$4)=2 ---> Green
=RANK(B1;$B$4:$B$4)=3 ---> Yellow

=RANK(B6;$B$6:$B$10)=1 --->Red
...
...