Top 10%, top 10 and above a threshold.

[Graham C1600]

Hi,

Suppose I have a list as per below :-

[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Graham[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Simon[/TD]
[TD]1[/TD]
[TD]32[/TD]
[TD]4[/TD]
[TD]332[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]4[/TD]
[TD]43[/TD]
[TD]6[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Brendan[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]34[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]8[/TD]
[TD]45[/TD]
[TD]77[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Stephen[/TD]
[TD]5[/TD]
[TD]32[/TD]
[TD]43[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]Taylor[/TD]
[TD]3[/TD]
[TD]45[/TD]
[TD]23[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]2[/TD]
[TD]34[/TD]
[TD]56[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Andrew[/TD]
[TD]9[/TD]
[TD]67[/TD]
[TD]23[/TD]
[TD]88[/TD]
[/TR]
</tbody>[/TABLE]

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 :-
[TABLE="width: 200"]
<tbody>[TR]
[TD]Andrew[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Brendan[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Taylor[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

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
Graham	Andrew
Simon	Sarah
Richard	Brendan
Brendan	Stephen
Sarah	Taylor
Stephen
Taylor
Mark
Andrew

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]332[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]23[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]33[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Database​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=LARGE($B$2:$B$10,ROWS(G$2:G2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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)),"")