# Top 5 high scores

#### americanpie3

##### Board Regular
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).

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

#### americanpie3

##### Board Regular

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

</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])

</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])}

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

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

</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])

</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])}

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

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)

#### Fluff

##### MrExcel MVP, Moderator
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
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
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

Replies
3
Views
101
Replies
10
Views
170
Replies
3
Views
156
Replies
1
Views
190
Replies
0
Views
125

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.

### Which adblocker are you using?

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

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