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

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
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
35,764
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
35,764
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
35,764
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,089,766
Messages
5,410,303
Members
403,309
Latest member
chaithra

This Week's Hot Topics

Top