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]

Sharing files privately is against board policy, so that's not an option.
How many rows of data do you have?

 

[americanpie3]

Did not know that I apologize

70 rows

 

[Fluff]

Unfortunately 70 rows is a bit much to put in a thread.
If you cut the data down to about 20 rows, do you still get the same problems?
If so you can use one of the add-ins available here https://www.mrexcel.com/forum/about-board/508133-attachments.html to include the data in a post

 

[americanpie3]

Here is my results page:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Standings			Leaders
2				Season
3	Division 1			Average Men			Women			Highest Close Men			Women
4		Team Finishes		Donald	64.17		Colette	41.28		Normand	77		Claudette	24
5	Team 4	1		Daniel	52.56		Sylvie F	39.28		Donald	54			3
6	Team 5	3		Randy	51.33		Sylvie G	39.11		Roland	40		Roy	2
7	Team 1	2		J-C	49.00		Phillis	35.67		Marc-Andr	40		Sylvie G	0
8	Team 2	3		Normand	45.94		Sylvie M	34.61		Yves	40			0
9
10	Division 2
11		Team Finishes		Closes Men			Women			High Score Men			Women
12	Team 7	3		Marc-Andr	3		Roy	1		Donald	140			100
13	Team 6	2		Donald	3		Claudette	1		Randy	140			96
14	Team 8	2		Daniel	3		Normand	1		Daniel	140		Normand	85
15	Team 3	5		Roland	2		Sylvie G	0		Roy	140		Sylvie R	77
16				Pierre	2			0		Marc-Andr	107		Sylvie G	76
StatsWeek1

 

[americanpie3]

Here is my page where the results page gets it's data from:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team 1	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
2	J-C	M	49.00	49.00	93	93	0	0	0	0
3	Jonathan	M	32.28	32.28	66	66	0	0	0	0
4	Roland	M	38.83	38.83	92	92	2	2	40	40
5
6
7	Team 2	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
8	Sylvain	M	42.00	42.00	92	92	0	0	0	0
9	Marc-Andr	M	39.33	39.33	107	107	3	3	40	40
10	Sylvie F	F	39.28	39.28	100	100	0	0	0	0
11
12
13	Team 3	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
14	Donald	M	64.17	64.17	140	140	3	3	54	54
15	Phillis	F	35.67	35.67	96	96	0	0	0	0
16	Pierre	M	37.61	37.61	61	61	2	2	10	10
17
18
19	Team 4	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
20	Randy	M	51.33	51.33	140	140	1	1	20	20
21	Patrick	M	44.00	44.00	98	98	0	0	0	0
22	Ren	M	31.78	31.78	60	60	0	0	0	0
23
24
25	Team 5	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
26	Daniel	M	52.56	52.56	140	140	3	3	32	32
27	Sylvie G	F	39.11	39.11	76	76	0	0	0	0
28	Manon	F	30.78	30.78	65	65	0	0	0	0
29
30
31	Team 6	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
32	Michel	M	45.39	45.39	95	95	1	1	24	24
33	Yves	M	37.83	37.83	74	74	1	1	40	40
34	Michel F	M	0.00	0.00	0	0	0	0	0	0
35
36
37	Team 7	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
38	Roy	M	36.22	36.22	140	140	1	1	2	2
39	Claudette	F	34.22	34.22	76	76	1	1	24	24
40	Colette	F	41.28	41.28	85	85	1	1	3	3
41
42
43	Team 8	Sexe	Season Avg	Avg Last week	High Score Season	High Score Last Week	Finishes Season	Finishes Last Week	Highest Finish Season	Highest Finish Last Week
44	Normand	M	45.94	45.94	85	85	1	1	77	77
45	Sylvie R	F	32.61	32.61	77	77	1	1	2	2
46	Sylvie M	F	34.61	34.61	65	65	0	0	0	0
SeasonSummary1

 

[americanpie3]

Formula E4 - =AGGREGATE(14,6,(SeasonSummary1!$C$2:$C$70)*(SeasonSummary1!$B$2:$B$70="M"),ROW(A1))
Formula D4 - =INDEX(SeasonSummary1!$A$1:$A$70,AGGREGATE(15,6,(ROW(SeasonSummary1!$A$1:$A$70)-ROW($A$1)+1)/(SeasonSummary1!$C$1:$C$70=E4),COUNTIF(E$4:E4,E4)))

Those seem to work for the Averages for men and women.

Formula in N4 - =AGGREGATE(14,6,(SeasonSummary1!$I$2:$I$70)*(SeasonSummary1!$B$2:$B$70="F"),ROW(A1))
Formula in M4 - =IFERROR(INDEX(SeasonSummary1!$A$1:$A$70,AGGREGATE(15,6,(ROW(SeasonSummary1!$A$1:$A$70)-ROW($A$1)+1)/((SeasonSummary1!$I$1:$I$70=N4)*(SeasonSummary1!$B$2:$B$70="F")),COUNTIF(N$4:N4,N4))),"")

So since there is an IFERROR = "" not sure what it sees as an error

 

[Fluff]

Thanks for the data, you have one slight typo in the formula for M4.
instead of
SeasonSummary1!$B$2:$B$70="F"
it should be
SeasonSummary1!$B$1:$B$70="F"

 

[americanpie3]

Wow. I can't believe that typo (that I didn't think it was a typo considering stats start there) would be the error. Thank you so very much. If I knew you I'd buy you a cake!

 

[Fluff]

You're welcome & thanks for the feedback

 

[americanpie3]

My pleasure. This simplifies a whole lot.

I will have another question to post about average scores that accumulates as the season goes along. My issue is that the player must always be on like A2 of every sheet of the current week rather than A2 one week then A3 another or A22 the next.

Anyway, you will see my post.

Cheers!