Top 10%, top 10 and above a threshold.

[Graham C1600]

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.

Thanks in advance.

 

[JimM]

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

 

[Graham C1600]

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]

Another option, that will allow for duplicate values

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

Cell Formulas
Range	Formula
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))

 

[JimM]

Sorry, my bad

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

 

[Graham C1600]

Another option, that will allow for duplicate values

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Database​

Worksheet Formulas Cell Formula 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)) <thead> </thead><tbody> </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]

How do you define the top 10%?

 

[Graham C1600]

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]

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]

To do numbers > 5 use

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

Cell Formulas
Range	Formula
F2	=IFERROR(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	=IFERROR(AGGREGATE(14,6,$B$2:$B$10/($B$2:$B$10>$G$1),ROWS(G$2:G2)),"")