Top 5 high scores

[americanpie3]

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.

 

[Fluff]

How about

Book1
	A	B	C	D	E
1	Dan	63		Standings	Name
2	Bill	54		104	Jack
3	Steve	73		73	Steve
4	Ben	38		73	Craig
5	Patrick	45		63	Dan
6	Jack	104		54	Bill
7	Craig	73
Sheet1

Cell Formulas
Range	Formula
D2	=LARGE($B$1:$B$7,ROW(A1))
E2	=INDEX($A$1:$A$7,AGGREGATE(15,6,(ROW($A$1:$A$7)-ROW($A$1)+1)/($B$1:$B$7=D2),COUNTIF(D$2:D2,D2)))

 

[americanpie3]

How about

Book1
	A	B	C	D	E
1	Dan	63		Standings	Name
2	Bill	54		104	Jack
3	Steve	73		73	Steve
4	Ben	38		73	Craig
5	Patrick	45		63	Dan
6	Jack	104		54	Bill
7	Craig	73
Sheet1

Cell Formulas
Range	Formula
D2	=LARGE($B$1:$B$7,ROW(A1))
E2	=INDEX($A$1:$A$7,AGGREGATE(15,6,(ROW($A$1:$A$7)-ROW($A$1)+1)/($B$1:$B$7=D2),COUNTIF(D$2:D2,D2)))

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]

How about using Fluff's example. In G1 you either place an "M" or an "F".

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Dan	63	M	Standings	Name
2	Bill	54	M	76	Elise
3	Elise	76	F	37	Sara
4	Steve	73	M
5	Ben	38	M
6	Patrick	45	M
7	Sara	37	F
8	Jack	104	M
9	Craig	73	M

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

Sheet1​

Worksheet Formulas Cell Formula 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 Cell Formula 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]

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]

How about using Fluff's example. In G1 you either place an "M" or an "F".

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Dan	63	M	Standings	Name
2	Bill	54	M	76	Elise
3	Elise	76	F	37	Sara
4	Steve	73	M
5	Ben	38	M
6	Patrick	45	M
7	Sara	37	F
8	Jack	104	M
9	Craig	73	M

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

Sheet1​

Worksheet Formulas Cell Formula 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 Cell Formula 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]

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]

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]

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]

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

Book1
	A	B	C	D	E	F	G
1	Dan	63	M	Standings	Name	Standings	Name
2	Bill	54	M	104	Jack	84	Sue
3	Elise	76	F	73	Steve	76	Elise
4	Steve	73	M	73	Craig	37	Sara
5	Ben	38	M	63	Dan	0
6	Patrick	45	M	55	Dave	0
7	Sara	37	F
8	Jack	104	M
9	Craig	73	M
10		a
11		b
12		c
13	Sue	84	F
14	Dave	55	M
Sheet1

Cell Formulas
Range	Formula
D2	=AGGREGATE(14,6,($B$1:$B$14)*($C$1:$C$14="M"),ROW(A1))
E2	=INDEX($A$1:$A$14,AGGREGATE(15,6,(ROW($A$1:$A$14)-ROW($A$1)+1)/($B$1:$B$14=D2),COUNTIF(D$2:D2,D2)))
F2	=AGGREGATE(14,6,($B$1:$B$14)*($C$1:$C$14="F"),ROW(A1))
G2	=IFERROR(INDEX($A$1:$A$14,AGGREGATE(15,6,(ROW($A$1:$A$14)-ROW($A$1)+1)/($B$1:$B$14=F2),COUNTIF(F$2:F2,F2))),"")