Top 10%, top 10 and above a threshold.

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
Hi,

Suppose I have a list as per below :-

ABCDE
Graham112512
Simon1324332
Richard443623
Brendan6123445
Sarah8457733
Stephen5324345
Taylor3452366
Mark2345677
Andrew9672388

<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 :-
Andrew9
Sarah8
Brendan6
Richard4
Taylor3

<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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
636
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
Joined
Feb 17, 2018
Messages
60
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
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
Another option, that will allow for duplicate values

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Graham</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">5</td><td style="text-align: right;;">12</td><td style=";">Andrew</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Simon</td><td style="text-align: right;;">1</td><td style="text-align: right;;">32</td><td style="text-align: right;;">4</td><td style="text-align: right;;">332</td><td style=";">Sarah</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Richard</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">6</td><td style="text-align: right;;">23</td><td style=";">Brendan</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Brendan</td><td style="text-align: right;;">6</td><td style="text-align: right;;">12</td><td style="text-align: right;;">34</td><td style="text-align: right;;">45</td><td style=";">Stephen</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Sarah</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;">77</td><td style="text-align: right;;">33</td><td style=";">Taylor</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Stephen</td><td style="text-align: right;;">5</td><td style="text-align: right;;">32</td><td style="text-align: right;;">43</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Taylor</td><td style="text-align: right;;">5</td><td style="text-align: right;;">45</td><td style="text-align: right;;">23</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Mark</td><td style="text-align: right;;">2</td><td style="text-align: right;;">34</td><td style="text-align: right;;">56</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Andrew</td><td style="text-align: right;;">9</td><td style="text-align: right;;">67</td><td style="text-align: right;;">23</td><td style="text-align: right;;">88</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Database</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=INDEX(<font color="Blue">$A$2:$A$10,AGGREGATE(<font color="Red">15,6,(<font color="Green">ROW(<font color="Purple">$A$2:$A$10</font>)-ROW(<font color="Purple">$A$2</font>)+1</font>)/(<font color="Green">$B$2:$B$10=G2</font>),COUNTIF(<font color="Green">G$2:G2,G2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=LARGE(<font color="Blue">$B$2:$B$10,ROWS(<font color="Red">G$2:G2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
636
Sorry, my bad

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

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
Another option, that will allow for duplicate values

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))

<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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
How do you define the top 10%?
 

Graham C1600

Board Regular
Joined
Feb 17, 2018
Messages
60
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,150
Office Version
365
Platform
Windows
To do numbers > 5 use

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">A</td><td style=";">B</td><td style=";">C</td><td style=";">D</td><td style=";">E</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Graham</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">5</td><td style="text-align: right;;">12</td><td style=";">Andrew</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Simon</td><td style="text-align: right;;">1</td><td style="text-align: right;;">32</td><td style="text-align: right;;">4</td><td style="text-align: right;;">332</td><td style=";">Sarah</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Richard</td><td style="text-align: right;;">4</td><td style="text-align: right;;">43</td><td style="text-align: right;;">6</td><td style="text-align: right;;">23</td><td style=";">Brendan</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Brendan</td><td style="text-align: right;;">6</td><td style="text-align: right;;">12</td><td style="text-align: right;;">34</td><td style="text-align: right;;">45</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Sarah</td><td style="text-align: right;;">8</td><td style="text-align: right;;">45</td><td style="text-align: right;;">77</td><td style="text-align: right;;">33</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Stephen</td><td style="text-align: right;;">5</td><td style="text-align: right;;">32</td><td style="text-align: right;;">43</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Taylor</td><td style="text-align: right;;">3</td><td style="text-align: right;;">45</td><td style="text-align: right;;">23</td><td style="text-align: right;;">66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Mark</td><td style="text-align: right;;">2</td><td style="text-align: right;;">34</td><td style="text-align: right;;">56</td><td style="text-align: right;;">77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Andrew</td><td style="text-align: right;;">9</td><td style="text-align: right;;">67</td><td style="text-align: right;;">23</td><td style="text-align: right;;">88</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Database</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$10,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$A$2:$A$10</font>)-ROW(<font color="Teal">$A$2</font>)+1</font>)/(<font color="Purple">$B$2:$B$10=G2</font>),COUNTIF(<font color="Purple">G$2:G2,G2</font>)</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IFERROR(<font color="Blue">AGGREGATE(<font color="Red">14,6,$B$2:$B$10/(<font color="Green">$B$2:$B$10>$G$1</font>),ROWS(<font color="Green">G$2:G2</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,090,474
Messages
5,414,754
Members
403,543
Latest member
Phx007

This Week's Hot Topics

Top