Top 5 high scores

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
Hello,

Is there a way to have a result of the top 5 highest scores? Here's my example.

Entered manually:

A1 = Dan - B1 = 63
A2 = Bill - B2 = 54
A3 = Steve - B3 = 73
A4 = Ben - B4 = 38
A5 = Patrick - B5 = 45
A6 = Jack - B6 = 104
A7 = Craig - B7 = 73

I put the tie on purpose because it can happen but not likely

So on D1 I wrote STANDINGS and E1 I put POINTS

So from D2 to D6 I want to formula to know that 104 is the high score and put the person's name and put the person's name the other column (E).

Thanks for your input.
 

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
How about

<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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Dan</td><td style="text-align: right;;">63</td><td style="text-align: right;;"></td><td style=";">Standings</td><td style=";">Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bill</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style=";">Jack</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Steve</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;">73</td><td style=";">Steve</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Ben</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;">73</td><td style=";">Craig</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Patrick</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;">63</td><td style=";">Dan</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Jack</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style="text-align: right;;">54</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Craig</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">D2</th><td style="text-align:left">=LARGE(<font color="Blue">$B$1:$B$7,ROW(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$7,AGGREGATE(<font color="Red">15,6,(<font color="Green">ROW(<font color="Purple">$A$1:$A$7</font>)-ROW(<font color="Purple">$A$1</font>)+1</font>)/(<font color="Green">$B$1:$B$7=D2</font>),COUNTIF(<font color="Green">D$2:D2,D2</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
How about

<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 /></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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Dan</td><td style="text-align: right;;">63</td><td style="text-align: right;;"></td><td style=";">Standings</td><td style=";">Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bill</td><td style="text-align: right;;">54</td><td style="text-align: right;;"></td><td style="text-align: right;;">104</td><td style=";">Jack</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Steve</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;">73</td><td style=";">Steve</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Ben</td><td style="text-align: right;;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;">73</td><td style=";">Craig</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Patrick</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;">63</td><td style=";">Dan</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Jack</td><td style="text-align: right;;">104</td><td style="text-align: right;;"></td><td style="text-align: right;;">54</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Craig</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">D2</th><td style="text-align:left">=LARGE(<font color="Blue">$B$1:$B$7,ROW(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$7,AGGREGATE(<font color="Red">15,6,(<font color="Green">ROW(<font color="Purple">$A$1:$A$7</font>)-ROW(<font color="Purple">$A$1</font>)+1</font>)/(<font color="Green">$B$1:$B$7=D2</font>),COUNTIF(<font color="Green">D$2:D2,D2</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Thank you for the reply.

I tried your formula and 1st, it did not show the biggest result. 2nd why ROW(A1)).

Another wrench and I'm sorry I didn't mention it earlier.

If in my example you also put women Let's say Sara 37 and Elise and 76. Then in the 1st example you would have rankings for men and then put seperate rankings for women.
I have already identified the women from men by putting an F or M in the very next column in the teams page.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,148
Office Version
2016
Platform
Windows
How about using Fluff's example. In G1 you either place an "M" or an "F".

Excel 2016 (Windows) 64 bit
ABCDE
1Dan63MStandingsName
2Bill54M76Elise
3Elise76F37Sara
4Steve73M
5Ben38M
6Patrick45M
7Sara37F
8Jack104M
9Craig73M

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

Worksheet Formulas
CellFormula
E2=IF(N([COLOR=#0]D2),INDEX([COLOR=#0]$A$1:$A$9,AGGREGATE([COLOR=#00]15,6,([COLOR=#00b00]ROW([COLOR=#0]$A$1:$A$9[/COLOR])-ROW([COLOR=#0]$A$1[/COLOR])+1[/COLOR])/([COLOR=#00b00]$B$1:$B$9=D2[/COLOR]),COUNTIF([COLOR=#00b00]D$2:D2,D2[/COLOR])[/COLOR])[/COLOR]),""[/COLOR])

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D2{=IFERROR(LARGE([COLOR=#0]IF([COLOR=#00]$C$1:$C$9=$G$1,$B$1:$B$9),ROW([COLOR=#00]A1[/COLOR])[/COLOR]),""[/COLOR])}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



<tbody>
</tbody>

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
I tried your formula and 1st, it did not show the biggest result.
Did you adjust the range to suit your data? If so what did you change it to?
 

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
How about using Fluff's example. In G1 you either place an "M" or an "F".

Excel 2016 (Windows) 64 bit
ABCDE
1Dan63MStandingsName
2Bill54M76Elise
3Elise76F37Sara
4Steve73M
5Ben38M
6Patrick45M
7Sara37F
8Jack104M
9Craig73M

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

Worksheet Formulas
CellFormula
E2=IF(N([COLOR=#0]D2),INDEX([COLOR=#0]$A$1:$A$9,AGGREGATE([COLOR=#00]15,6,([COLOR=#00b00]ROW([COLOR=#0]$A$1:$A$9[/COLOR])-ROW([COLOR=#0]$A$1[/COLOR])+1[/COLOR])/([COLOR=#00b00]$B$1:$B$9=D2[/COLOR]),COUNTIF([COLOR=#00b00]D$2:D2,D2[/COLOR])[/COLOR])[/COLOR]),""[/COLOR])

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D2{=IFERROR(LARGE([COLOR=#0]IF([COLOR=#00]$C$1:$C$9=$G$1,$B$1:$B$9),ROW([COLOR=#00]A1[/COLOR])[/COLOR]),""[/COLOR])}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



<tbody>
</tbody>

OK. I think I might be able to work with this one. One last question. With =large can I do multiple ranges. Example =large($C$1:$C$9) but then to also incluse in the =large($C$12:$C$17). The reason why I ask is because there is information between the lines which is divisions and I have letters in there.
 

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
Did you adjust the range to suit your data? If so what did you change it to?
It worked when I did =LARGE($B$1:$B$7,1)

The only adjustment I made was the letters
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
There should be no need to do that. Row(A1) will return 1 & when you drag the formula down it will become Row(B1) which returns 2.
 

americanpie3

Board Regular
Joined
Jul 16, 2003
Messages
180
There should be no need to do that. Row(A1) will return 1 & when you drag the formula down it will become Row(B1) which returns 2.
Actually when I drag it goes A2 A3 A4 and so on.

Did you see my question about multiple ranges? When I try I keep getting too many arguments.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
You're quite right it does indeed go A1, A2 etc. Not sure why I said A1, B1 etc, but the end result is the same.

You don't need to worry about having non-numeric values with

<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=";">Dan</td><td style="text-align: right;;">63</td><td style=";">M</td><td style="text-align: center;;">Standings</td><td style="text-align: center;;">Name</td><td style="text-align: center;;">Standings</td><td style="text-align: center;;">Name</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bill</td><td style="text-align: right;;">54</td><td style=";">M</td><td style="text-align: right;;">104</td><td style=";">Jack</td><td style="text-align: right;;">84</td><td style=";">Sue</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Elise</td><td style="text-align: right;;">76</td><td style=";">F</td><td style="text-align: right;;">73</td><td style=";">Steve</td><td style="text-align: right;;">76</td><td style=";">Elise</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Steve</td><td style="text-align: right;;">73</td><td style=";">M</td><td style="text-align: right;;">73</td><td style=";">Craig</td><td style="text-align: right;;">37</td><td style=";">Sara</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Ben</td><td style="text-align: right;;">38</td><td style=";">M</td><td style="text-align: right;;">63</td><td style=";">Dan</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Patrick</td><td style="text-align: right;;">45</td><td style=";">M</td><td style="text-align: right;;">55</td><td style=";">Dave</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Sara</td><td style="text-align: right;;">37</td><td style=";">F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></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=";">Jack</td><td style="text-align: right;;">104</td><td style=";">M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></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=";">Craig</td><td style="text-align: right;;">73</td><td style=";">M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></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=";"></td><td style="text-align: right;;">a</td><td style=";"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;">11</td><td style=";"></td><td style="text-align: right;;">b</td><td style=";"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;">12</td><td style=";"></td><td style="text-align: right;;">c</td><td style=";"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></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;">13</td><td style=";">Sue</td><td style="text-align: right;;">84</td><td style=";">F</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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;">14</td><td style=";">Dave</td><td style="text-align: right;;">55</td><td style=";">M</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;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)">Sheet1</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)">D2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,(<font color="Red">$B$1:$B$14</font>)*(<font color="Red">$C$1:$C$14="M"</font>),ROW(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$14,AGGREGATE(<font color="Red">15,6,(<font color="Green">ROW(<font color="Purple">$A$1:$A$14</font>)-ROW(<font color="Purple">$A$1</font>)+1</font>)/(<font color="Green">$B$1:$B$14=D2</font>),COUNTIF(<font color="Green">D$2:D2,D2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">14,6,(<font color="Red">$B$1:$B$14</font>)*(<font color="Red">$C$1:$C$14="F"</font>),ROW(<font color="Red">A1</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">INDEX(<font color="Red">$A$1:$A$14,AGGREGATE(<font color="Green">15,6,(<font color="Purple">ROW(<font color="Teal">$A$1:$A$14</font>)-ROW(<font color="Teal">$A$1</font>)+1</font>)/(<font color="Purple">$B$1:$B$14=F2</font>),COUNTIF(<font color="Purple">F$2:F2,F2</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,095,235
Messages
5,443,278
Members
405,223
Latest member
Industrial_Eng_SA

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top