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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. 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,245
Office Version
  1. 2016
Platform
  1. 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
56,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

ADVERTISEMENT

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
56,562
Office Version
  1. 365
Platform
  1. 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
56,562
Office Version
  1. 365
Platform
  1. 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,129,574
Messages
5,637,163
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top