# Top 10%, top 10 and above a threshold.

#### Graham C1600

##### Board Regular
Hi,

Suppose I have a list as per below :-

 A B C D E Graham 1 12 5 12 Simon 1 32 4 332 Richard 4 43 6 23 Brendan 6 12 34 45 Sarah 8 45 77 33 Stephen 5 32 43 45 Taylor 3 45 23 66 Mark 2 34 56 77 Andrew 9 67 23 88

<tbody>
</tbody>

In my actual excel the list is much larger but I hope you get what I'm trying to achieve.

So in cell G1 for instance I want to show the top 5 numbers from column b starting with the largest and coming down, but I need it so show column A & B. So it would look something like this :-
 Andrew 9 Sarah 8 Brendan 6 Richard 4 Taylor 3

<tbody>
</tbody>

Then again in Cell I1 I want the top 10% of Column B to be displayed as above. Then in cell K1 I want a formula to list the top 5 based on a number I want to put into the formula.

Hope this makes sense.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### JimM

##### Well-known Member
To get the top 5 you could use the LARGE function

In cell G1 =LARGE(B1:B9,1)
In cell G2 =LARGE(B1:B9,2)

To get the names try

In cell F1 =INDEX(\$A\$1:\$E\$9,MATCH(H1,\$B\$1:\$B\$9),1) and copy down

Obviously change the ranges to suit

Last edited:

#### Graham C1600

##### Board Regular
To get the top 5 you could use the LARGE function

In cell G1 =LARGE(B1:B9,1)
In cell G2 =LARGE(B1:B9,2)

To get the names try

In cell F1 =INDEX(\$A\$1:\$E\$9,MATCH(H1,\$B\$1:\$B\$9),1) and copy down

Obviously change the ranges to suit
Hi,

Thanks for this. I'm a bit confused as to the reference to cell H1. What does this do as there is no data in H1. Should this be G1 as per the large function ?

Thanks

#### Fluff

##### MrExcel MVP, Moderator
Another option, that will allow for duplicate values

#### JimM

##### Well-known Member

The H1 should actually be G1 (ie the cell that has the number 9 in it)

#### Graham C1600

##### Board Regular
Another option, that will allow for duplicate values

ABCDEFG
2Graham112512Andrew9
3Simon1324332Sarah8
4Richard443623Brendan6
5Brendan6123445Stephen5
6Sarah8457733Taylor5
7Stephen5324345
8Taylor5452366
9Mark2345677
10Andrew9672388

</tbody>
Database

Worksheet Formulas
CellFormula
F2=INDEX(\$A\$2:\$A\$10,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1)/(\$B\$2:\$B\$10=G2),COUNTIF(G\$2:G2,G2)))
G2=LARGE(\$B\$2:\$B\$10,ROWS(G\$2:G2))

</tbody>

<tbody>
</tbody>
Thanks this worked a treat. Just need to get the top % now and the number above the threshold I set in the formula.

Thanks again.

#### Fluff

##### MrExcel MVP, Moderator
How do you define the top 10%?

#### Graham C1600

##### Board Regular
How do you define the top 10%?
Apologies, should have said the top 10% with the highest numbers. Not a good example on what i'm showing but my actual spreadsheet has around 500 lines of data which I want to show the top 10% with the highest numbers.

#### Graham C1600

##### Board Regular
Thanks this worked a treat. Just need to get the top % now and the number above the threshold I set in the formula.

Thanks again.
Hi, is there any easy way to also show numbers greater than 5 only ? Thanks

#### Fluff

##### MrExcel MVP, Moderator
To do numbers > 5 use