Highlight Top 3 Value/s with Ties

[papajups]

I have column A with a list of numbers, and I am trying to highlight the top 3 highest value/s. However, conditional formatting is not working properly if there are duplicate values.

Highest value/s – Color Blue
2^nd Highest value/s – Color Yellow
3^rd Highest value/s – Color Green
Other Values should be empty (no color)

A1=5
A2=5
A3=5
A4=4
A5=4
A6=4
A7=3
A8=3
A9=3
A10=2
A11=1
A12=0

From the above example, cells A1:A3 must be highlighted in ‘blue’, while A4:A6 should be in ‘yellow’, and the a7:a9 should be in ‘green’.

Is there an excel formula or macro to accomplish this?

Your help will be greatly appreciated.


regards,
Papajups

 

[Eric W]

Try this:

Select column A. Click Conditional formatting > New Rule > Use a formula > and enter:

=A1=MAX(A:A)

choose a blue fill color.

Repeat with:

=A1=LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1)

and a yellow fill color, and

=A1=LARGE(A:A,COUNTIF(A:A,">="&LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1))+1)

and a green fill color.

 

[MySpreadsheetLab]

Great solution Eric!! Thanks

 

[papajups]

Thank you for your help, Eric.

But what if I only want range A1:A25 to be affected? As i have cell A26 as the Total - it will always be the highest value if I apply the above formulas.


regards,
Papajups

 

[papajups]

Eric,


Please ignore my previous reply. I was able to tweak the formula.

Big thanks again for your help. I really appreciate it.


regards,
Papajups