Ranking Top 3 with Conditional Formatting

[Renier]

I would appreciate some help with a formula please. I would like to use conditional formatting to highlight the top 3 values in column A which has a column B value of more than 80 000. When conditional formatting is done it should look like this:

Thanks in advance.

 

[FormR]

Hi, welcome to the forum!

Here is one option:

Book1
	A	B
1	58%	106398
2	48%	90961
3	29%	69101
4	28%	65961
5	28%	78398
6	27%	62548
7	24%	68712
8	22%	39405
9	21%	154507
10	20%	149457
11	19%	165427
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B11	Expression	=ISNUMBER(MATCH($A1,LARGE(IF($B$1:$B$11>80000,$A$1:$A$11),ROW(INDIRECT("1:3"))),0))	text	NO

 

[Renier]

=ISNUMBER(MATCH($A1,LARGE(IF($B$1:$B$11>80000,$A$1:$A$11),ROW(INDIRECT("1:3"))),0))

Worked perfectly thank you very much.

 

[Bo_Ry]

Select A1:B11
Home > conditional formatting > New rules > use a formula

=(SUM(($B$1:$B$11>80000)*($A$1:$A$11>$A1))/($B1>80000))<3

format > fill green > OK,OK

 

[Renier]

Select A1:B11
Home > conditional formatting > New rules > use a formula

=(SUM(($B$1:$B$11>80000)*($A$1:$A$11>$A1))/($B1>80000))<3

format > fill green > OK,OK

also worked 100% thank you very much.

 

[FormR]

Hi, and here's another option where you can use full column references without too much impact on performance.

=AND(COUNTIFS($A:$A,">"&$A1,$B:$B,">80000")<3,$B1>80000)

 

[Renier]

Hi, and here's another option where you can use full column references without too much impact on performance.

=AND(COUNTIFS($A:$A,">"&$A1,$B:$B,">80000")<3,$B1>80000)

Thank you very much.