Average of top 150 cells from one column

[knoll126]

Hi,

I have Column A that has 500 names in it. Column C has 500 lines with values from -7 to 35. I would like to return the average of the top 150 cells in Column C. The output would be on a second tab.

The second question is I have Column B that has data in the cells. I would like to be able to see the average of those cells based on the top 150 cells in Column C. Again with the output on a second tab.

Any help would be appericaited. When I was googling for answers I was only find results for top 3 or so values and not 150!

Data Example.xlsx
	A	B	C
1	Full Name	Strato ABs	NERP OFF WGHT
2	Mike Trout	36	30.28
3	Bryce Harper	147	27.35
4	Byron Buxton	62	26.33
5	Jose Siri	11.8	23.96
6	Fernando Tatis Jr.	135	24.35
7	Juan Soto	161.8	22.9
8	Brandon Belt	93.3	22.07
9	Jesse Winker	119	19.65
10	Starling Marte	127.5	21.66
11	Vladimir Guerrero Jr.	172.5	21.51
12	Cedric Mullins II	165.3	21.27
13	Ronald Acuna Jr.	86.5	23.19
14	Kyle Schwarber	115.8	20.82
15	Brandon Lowe	150.8	19.21
16	Jared Walsh	144.5	17.64
17	Joey Votto	131.3	19.41
18	Rafael Devers	163.3	19.39
19	Brandon Nimmo	94.8	20.41
20	Brandon Crawford	134.8	18.58
21	Ramon Urias	72.5	17.91
22	Kyle Isbel	20.8	18.64
23	Mitch Garver	59.5	18.74
24	Frank Schwindel	64.5	21.61
25	Brett Phillips	71.5	16.53
26	J.D. Davis	50.8	19.3
27	Rafael Ortega	81.5	16.03
28	Michael Brantley	125.5	18.05
29	Thairo Estrada	32.5	16.9
30	Kyle Tucker	139.8	19.73
31	A.J. Pollock	103.5	18.64
32	Luis Robert	72.3	6.91
33	Alex Verdugo	148.8	16.47
34	Freddie Freeman	171.3	17.84
35	Tyler O'Neill	130	20.39
36	Austin Riley	160.5	17.76
37	Riley Adams	28.5	17.47
38	Corey Seager	100.3	19.23
39	Santiago Espinal	61	18.96
40	Nick Castellanos	143	18.43
41	Adam Frazier	156.3	17.71
42	Tony Kemp	95.5	17.87
43	Bryan Reynolds	158.5	18.88
44	Xander Bogaerts	147.8	17.49
45	Will Smith	118	16.15
46	Jason Castro	43.5	14.77
47	Myles Straw	157.8	15.84
48	Yoan Moncada	151	16.78
49	Matt Olson	163.3	18.81
50	Marcus Semien	179.5	16.05
51	Shohei Ohtani	158.3	18.45
52	Alex Avila	27	13.6
53	Nico Hoerner	41.5	16.02
54	Nicky Lopez	136.5	16.81
55	Mookie Betts	133.5	17.09
56	Matt Vierling	18.8	17.53
57	Yuli Gurriel	147.3	17.3
58	Travis Jankowski	38.3	15.34
59	Max Schrock	33.3	12.12
60	J.D. Martinez	156.3	15.4
61	Aaron Judge	156.3	17.46
62	Trea Turner	159	19.29
63	Jonathan India	150.8	16.67
64	Jose Ramirez	156	16.1
65	Yasmani Grandal	91.5	17.88
66	Josh Harrison	134	15.47
67	Justin Turner	148.5	15.25
68	Kevin Kiermaier	95.3	15.36
69	Akil Baddoo	114.5	13.85
70	Brandon Marsh	64	14.43
71	Zack Collins	57.3	12.48
72	Austin Meadows	144.3	13.47
73	George Springer	84	16.28
74	Darin Ruf	77	17.15
75	Jacob Stallings	105.8	13.38
76	Joey Gallo	152.3	15.05
77	Jose Altuve	166.8	14.97
78	Tyler Stephenson	97.8	15.3
79	Buster Posey	112.8	17.28
80	Jeimer Candelario	155.5	15.26
81	Tim Anderson	137.3	15.62
82	Ty France	154.3	15.62
83	Luis Arraez	117.8	14.71
84	Pete Alonso	155.3	15.19
85	Carlos Correa	157.5	15.61
86	Brian Anderson	64.8	11.99
87	Connor Joe	51.3	15.92
88	Kris Bryant	143.8	15.54
89	Willy Adames	138.5	13.99
90	Whit Merrifield	176	14.82
91	J.P. Crawford	169.3	14.22
92	Mike Yastrzemski	129.8	12.39
93	Giancarlo Stanton	143.3	14.95
94	Max Muncy	145	16.13
95	C.J. Cron	132.5	15.65
96	Brian Goodwin	67	10.98
97	Ji-Man Choi	75.8	12.9
98	Raimel Tapia	131.8	13.42
99	Paul Goldschmidt	167.5	17.14
100	Jake Fraley	65	13.43
Example

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what exactly do you mean by the top 150 cells?

 

[knoll126]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what exactly do you mean by the top 150 cells?

Sorry, when referring to top I mean the top highest value. So if sorting from Largest to Smallest it would be the first 150 results.

Also using office 365 and updated it in my profile, thanks!

 

[Fluff]

Thanks for that. How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Full Name	Strato ABs	NERP OFF WGHT		50	Avg B	Avg C
2	Mike Trout	36	30.28			113.208	19.7382
3	Bryce Harper	147	27.35
4	Byron Buxton	62	26.33
5	Jose Siri	11.8	23.96
6	Fernando Tatis Jr.	135	24.35
7	Juan Soto	161.8	22.9
8	Brandon Belt	93.3	22.07
9	Jesse Winker	119	19.65
10	Starling Marte	127.5	21.66
11	Vladimir Guerrero Jr.	172.5	21.51
12	Cedric Mullins II	165.3	21.27
13	Ronald Acuna Jr.	86.5	23.19
14	Kyle Schwarber	115.8	20.82
15	Brandon Lowe	150.8	19.21
16	Jared Walsh	144.5	17.64
17	Joey Votto	131.3	19.41
18	Rafael Devers	163.3	19.39
19	Brandon Nimmo	94.8	20.41
20	Brandon Crawford	134.8	18.58
21	Ramon Urias	72.5	17.91
22	Kyle Isbel	20.8	18.64
23	Mitch Garver	59.5	18.74
24	Frank Schwindel	64.5	21.61
25	Brett Phillips	71.5	16.53
26	J.D. Davis	50.8	19.3
27	Rafael Ortega	81.5	16.03
28	Michael Brantley	125.5	18.05
29	Thairo Estrada	32.5	16.9
30	Kyle Tucker	139.8	19.73
31	A.J. Pollock	103.5	18.64
32	Luis Robert	72.3	6.91
33	Alex Verdugo	148.8	16.47
34	Freddie Freeman	171.3	17.84
35	Tyler O'Neill	130	20.39
36	Austin Riley	160.5	17.76
37	Riley Adams	28.5	17.47
38	Corey Seager	100.3	19.23
39	Santiago Espinal	61	18.96
40	Nick Castellanos	143	18.43
41	Adam Frazier	156.3	17.71
42	Tony Kemp	95.5	17.87
43	Bryan Reynolds	158.5	18.88
44	Xander Bogaerts	147.8	17.49
45	Will Smith	118	16.15
46	Jason Castro	43.5	14.77
47	Myles Straw	157.8	15.84
48	Yoan Moncada	151	16.78
49	Matt Olson	163.3	18.81
50	Marcus Semien	179.5	16.05
51	Shohei Ohtani	158.3	18.45
52	Alex Avila	27	13.6
53	Nico Hoerner	41.5	16.02
54	Nicky Lopez	136.5	16.81
55	Mookie Betts	133.5	17.09
56	Matt Vierling	18.8	17.53
57	Yuli Gurriel	147.3	17.3
58	Travis Jankowski	38.3	15.34
59	Max Schrock	33.3	12.12
60	J.D. Martinez	156.3	15.4
61	Aaron Judge	156.3	17.46
62	Trea Turner	159	19.29
63	Jonathan India	150.8	16.67
64	Jose Ramirez	156	16.1
65	Yasmani Grandal	91.5	17.88
66	Josh Harrison	134	15.47
67	Justin Turner	148.5	15.25
68	Kevin Kiermaier	95.3	15.36
69	Akil Baddoo	114.5	13.85
70	Brandon Marsh	64	14.43
71	Zack Collins	57.3	12.48
72	Austin Meadows	144.3	13.47
73	George Springer	84	16.28
74	Darin Ruf	77	17.15
75	Jacob Stallings	105.8	13.38
76	Joey Gallo	152.3	15.05
77	Jose Altuve	166.8	14.97
78	Tyler Stephenson	97.8	15.3
79	Buster Posey	112.8	17.28
80	Jeimer Candelario	155.5	15.26
81	Tim Anderson	137.3	15.62
82	Ty France	154.3	15.62
83	Luis Arraez	117.8	14.71
84	Pete Alonso	155.3	15.19
85	Carlos Correa	157.5	15.61
86	Brian Anderson	64.8	11.99
87	Connor Joe	51.3	15.92
88	Kris Bryant	143.8	15.54
89	Willy Adames	138.5	13.99
90	Whit Merrifield	176	14.82
91	J.P. Crawford	169.3	14.22
92	Mike Yastrzemski	129.8	12.39
93	Giancarlo Stanton	143.3	14.95
94	Max Muncy	145	16.13
95	C.J. Cron	132.5	15.65
96	Brian Goodwin	67	10.98
97	Ji-Man Choi	75.8	12.9
98	Raimel Tapia	131.8	13.42
99	Paul Goldschmidt	167.5	17.14
100	Jake Fraley	65	13.43
Sheet1

Cell Formulas
Range	Formula
F2:G2	F2	=LET(I,INDEX(SORT(B2:C100,2,-1),SEQUENCE(E1),{1,2}),CHOOSE({1,2},AVERAGE(INDEX(I,,1)),AVERAGE(INDEX(I,,2))))
Dynamic array formulas.

I've done it for the top 50, but just change the value in E1 to whatever you want.

 

[knoll126]

Thanks for that. How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Full Name	Strato ABs	NERP OFF WGHT		50	Avg B	Avg C
2	Mike Trout	36	30.28			113.208	19.7382
3	Bryce Harper	147	27.35
4	Byron Buxton	62	26.33
5	Jose Siri	11.8	23.96
6	Fernando Tatis Jr.	135	24.35
7	Juan Soto	161.8	22.9
8	Brandon Belt	93.3	22.07
9	Jesse Winker	119	19.65
10	Starling Marte	127.5	21.66
11	Vladimir Guerrero Jr.	172.5	21.51
12	Cedric Mullins II	165.3	21.27
13	Ronald Acuna Jr.	86.5	23.19
14	Kyle Schwarber	115.8	20.82
15	Brandon Lowe	150.8	19.21
16	Jared Walsh	144.5	17.64
17	Joey Votto	131.3	19.41
18	Rafael Devers	163.3	19.39
19	Brandon Nimmo	94.8	20.41
20	Brandon Crawford	134.8	18.58
21	Ramon Urias	72.5	17.91
22	Kyle Isbel	20.8	18.64
23	Mitch Garver	59.5	18.74
24	Frank Schwindel	64.5	21.61
25	Brett Phillips	71.5	16.53
26	J.D. Davis	50.8	19.3
27	Rafael Ortega	81.5	16.03
28	Michael Brantley	125.5	18.05
29	Thairo Estrada	32.5	16.9
30	Kyle Tucker	139.8	19.73
31	A.J. Pollock	103.5	18.64
32	Luis Robert	72.3	6.91
33	Alex Verdugo	148.8	16.47
34	Freddie Freeman	171.3	17.84
35	Tyler O'Neill	130	20.39
36	Austin Riley	160.5	17.76
37	Riley Adams	28.5	17.47
38	Corey Seager	100.3	19.23
39	Santiago Espinal	61	18.96
40	Nick Castellanos	143	18.43
41	Adam Frazier	156.3	17.71
42	Tony Kemp	95.5	17.87
43	Bryan Reynolds	158.5	18.88
44	Xander Bogaerts	147.8	17.49
45	Will Smith	118	16.15
46	Jason Castro	43.5	14.77
47	Myles Straw	157.8	15.84
48	Yoan Moncada	151	16.78
49	Matt Olson	163.3	18.81
50	Marcus Semien	179.5	16.05
51	Shohei Ohtani	158.3	18.45
52	Alex Avila	27	13.6
53	Nico Hoerner	41.5	16.02
54	Nicky Lopez	136.5	16.81
55	Mookie Betts	133.5	17.09
56	Matt Vierling	18.8	17.53
57	Yuli Gurriel	147.3	17.3
58	Travis Jankowski	38.3	15.34
59	Max Schrock	33.3	12.12
60	J.D. Martinez	156.3	15.4
61	Aaron Judge	156.3	17.46
62	Trea Turner	159	19.29
63	Jonathan India	150.8	16.67
64	Jose Ramirez	156	16.1
65	Yasmani Grandal	91.5	17.88
66	Josh Harrison	134	15.47
67	Justin Turner	148.5	15.25
68	Kevin Kiermaier	95.3	15.36
69	Akil Baddoo	114.5	13.85
70	Brandon Marsh	64	14.43
71	Zack Collins	57.3	12.48
72	Austin Meadows	144.3	13.47
73	George Springer	84	16.28
74	Darin Ruf	77	17.15
75	Jacob Stallings	105.8	13.38
76	Joey Gallo	152.3	15.05
77	Jose Altuve	166.8	14.97
78	Tyler Stephenson	97.8	15.3
79	Buster Posey	112.8	17.28
80	Jeimer Candelario	155.5	15.26
81	Tim Anderson	137.3	15.62
82	Ty France	154.3	15.62
83	Luis Arraez	117.8	14.71
84	Pete Alonso	155.3	15.19
85	Carlos Correa	157.5	15.61
86	Brian Anderson	64.8	11.99
87	Connor Joe	51.3	15.92
88	Kris Bryant	143.8	15.54
89	Willy Adames	138.5	13.99
90	Whit Merrifield	176	14.82
91	J.P. Crawford	169.3	14.22
92	Mike Yastrzemski	129.8	12.39
93	Giancarlo Stanton	143.3	14.95
94	Max Muncy	145	16.13
95	C.J. Cron	132.5	15.65
96	Brian Goodwin	67	10.98
97	Ji-Man Choi	75.8	12.9
98	Raimel Tapia	131.8	13.42
99	Paul Goldschmidt	167.5	17.14
100	Jake Fraley	65	13.43
Sheet1

Cell Formulas
Range	Formula
F2:G2	F2	=LET(I,INDEX(SORT(B2:C100,2,-1),SEQUENCE(E1),{1,2}),CHOOSE({1,2},AVERAGE(INDEX(I,,1)),AVERAGE(INDEX(I,,2))))
Dynamic array formulas.

I've done it for the top 50, but just change the value in E1 to whatever you want.

And if column B and C are not right next to each other? I am assuming the Sort expression will break.

Also I am not familiar with LET function and am curious on what exactly it is doing. I kind of understand that it is renaming a formula/column to be reference easier? What would be the equivalent if you weren't using Let?

 

[Fluff]

Yup, can you post some more realistic data.

 

[knoll126]

And I edited my comment above, but I understand the basic of LET expression (so you can reference it later without selecting the columns/formulas(?)). Is it possible to not use that, so I can see exactly what is happening to learn a little bit better? If not I can try and recreate it without the LET myself.

So in this table we have Strato AB in column E and NERP OFF WEIGHT in column R. Would like to still do the same comparision as of above with the same output.

Thank you!

Data Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Full Name	Bats	Pos	PAs	Strato ABs	Per 30	ABs	BBs	SB	CS	WAR	wRC+	wOBA	wOBA vs L	wOBA vs R	NERP vs lhp WGHT	NERP vs rhp WGHT	NERP OFF WGHT
2	Mike Trout	R	CF	146	36	1.2	117	27	2	0	2.3	190	0.451	0.346	0.498	4.05	26.22	30.28
3	Bryce Harper	L	RF	599	147	4.9	488	100	13	3	6.6	170	0.431	0.356	0.467	3.53	23.82	27.35
4	Byron Buxton	R	CF	254	62	2.07	235	13	9	1	4.2	169	0.419	0.418	0.419	4.99	21.34	26.33
5	Luis Robert	R	CF	296	72.3	2.41	275	14	6	1	3.2	157	0.399	0.509	0.367	6.91	15.52	28.98
6	Fernando Tatis Jr.	R	SS	546	135	4.5	478	62	25	4	6.1	156	0.403	0.398	0.404	4.47	19.88	24.35
7	Jose Siri	R	CF	49	11.8	0.39	46	1	3	1	0.3	160	0.401	0.449	0.375	3.86	20.1	23.96
8	Ronald Acuna Jr.	R	RF	360	86.5	2.88	297	49	17	6	4.2	157	0.412	0.44	0.404	5.4	17.79	23.19
9	Juan Soto	L	RF	654	161.8	5.39	502	145	9	7	6.6	163	0.42	0.372	0.448	3.3	19.6	22.9
10	Brandon Belt	L	1B	381	93.3	3.11	325	48	3	2	3.3	158	0.406	0.361	0.417	3.36	18.71	22.07
11	Starling Marte	R	CF	526	127.5	4.25	467	43	47	5	5.5	134	0.364	0.331	0.38	3.18	18.47	21.66
12	Frank Schwindel	R	1B	259	64.5	2.15	242	16	2	1	2.1	152	0.403	0.44	0.387	5.45	16.17	21.61
13	Vladimir Guerrero Jr.	R	1B	698	172.5	5.75	604	86	4	1	6.7	166	0.419	0.404	0.424	3.59	17.92	21.51
14	Cedric Mullins II	L	CF	675	165.3	5.51	602	59	30	8	5.3	136	0.372	0.339	0.391	3.43	17.85	21.27
15	Kyle Schwarber	L	LF	471	115.8	3.86	399	64	1	1	3.1	145	0.392	0.352	0.41	3.11	17.71	20.82
16	Brandon Nimmo	L	CF	386	94.8	3.16	325	54	5	4	3.5	137	0.369	0.372	0.368	3.83	16.59	20.41
17	Tyler O'Neill	R	LF	537	130	4.33	482	38	15	4	5.4	145	0.384	0.425	0.375	5.03	15.36	20.39
18	Kyle Tucker	L	RF	567	139.8	4.66	506	53	14	2	4.8	147	0.383	0.378	0.385	4.05	15.67	19.73
19	Jesse Winker	L	LF	485	119	3.97	423	53	1	0	3.2	148	0.403	0.263	0.449	1.05	18.6	19.65
20	Joey Votto	L	1B	533	131.3	4.38	448	77	1	0	3.6	140	0.391	0.308	0.43	2.1	17.31	19.41
21	Rafael Devers	L	3B	664	163.3	5.44	591	62	5	5	4.7	134	0.373	0.329	0.401	2.59	16.79	19.39
22	J.D. Davis	R	3B	211	50.8	1.69	179	24	1	0	1.6	130	0.357	0.305	0.381	3.32	15.97	19.3
23	Trea Turner	R	SS	646	159	5.3	595	41	32	5	6.9	142	0.386	0.477	0.355	6.11	13.18	19.29
24	Corey Seager	L	SS	409	100.3	3.34	353	48	1	1	3.7	147	0.389	0.39	0.389	4.01	15.22	19.23
25	Brandon Lowe	L	2B	615	150.8	5.03	535	68	7	1	5.2	137	0.363	0.284	0.399	1.53	17.68	19.21
26	Santiago Espinal	R	3B	246	61	2.03	222	22	6	1	2.2	115	0.344	0.351	0.339	3.75	15.21	18.96
27	Bryan Reynolds	S	CF	646	158.5	5.28	559	75	5	2	5.5	142	0.385	0.385	0.385	4.23	14.65	18.88
28	Matt Olson	L	1B	673	163.3	5.44	565	88	4	1	5	146	0.379	0.398	0.367	4.7	14.12	18.81
29	Mitch Garver	R	CA	243	59.5	1.98	207	31	1	1	2.1	137	0.372	0.312	0.417	2.46	16.28	18.74
30	A.J. Pollock	R	LF	422	103.5	3.45	384	30	9	1	3	137	0.375	0.366	0.379	3.1	15.54	18.64
31	Kyle Isbel	L	RF	83	20.8	0.69	76	7	2	0	0.4	109	0.333	0.377	0.325	2.31	16.33	18.64
32	Brandon Crawford	L	SS	549	134.8	4.49	483	56	11	3	5.5	139	0.377	0.308	0.403	2.17	16.42	18.58
33	Shohei Ohtani	L	RF	639	158.3	5.28	537	96	26	10	5.1	152	0.393	0.397	0.391	4.5	13.94	18.45
34	Nick Castellanos	R	RF	585	143	4.77	531	41	3	1	4.2	140	0.391	0.393	0.39	3.47	14.96	18.43
35	Michael Brantley	L	LF	508	125.5	4.18	469	33	1	0	2.1	123	0.347	0.252	0.399	2.22	15.84	18.05
36	Ramon Urias	R	SS	296	72.5	2.42	262	28	1	2	1.6	115	0.341	0.311	0.364	1.53	16.38	17.91
37	Yasmani Grandal	S	CA	375	91.5	3.05	279	87	0	0	3.7	159	0.402	0.44	0.389	4.96	12.92	17.88
38	Tony Kemp	L	2B	397	95.5	3.18	330	52	8	2	2.7	127	0.351	0.341	0.355	3.15	14.72	17.87
39	Freddie Freeman	L	1B	695	171.3	5.71	600	85	8	3	4.5	135	0.379	0.33	0.398	2.47	15.37	17.84
40	Ketel Marte	S	CF	374	92.8	3.09	340	31	2	0	2.9	139	0.385	0.482	0.343	6.51	11.26	17.77
41	Austin Riley	R	3B	662	160.5	5.35	590	52	0	1	4.2	135	0.379	0.322	0.396	2.41	15.35	17.76
42	Adam Frazier	L	2B	639	156.3	5.21	577	48	10	5	3.4	114	0.341	0.329	0.345	2.89	14.83	17.71
43	Jared Walsh	L	1B	585	144.5	4.82	530	48	2	1	2.8	127	0.357	0.241	0.415	0.06	17.58	17.64
44	Matt Vierling	R	1B	77	18.8	0.63	71	4	2	0	0.5	121	0.356	0.335	0.382	4.02	13.51	17.53
45	Xander Bogaerts	R	SS	603	147.8	4.93	529	62	5	1	5.2	130	0.368	0.355	0.375	2.98	14.51	17.49
46	Riley Adams	R	CA	120	28.5	0.95	99	15	0	0	0	105	0.332	0.301	0.355	2.24	15.24	17.47
47	Aaron Judge	R	RF	633	156.3	5.21	550	75	6	1	5.5	148	0.387	0.415	0.375	4.24	13.21	17.46
48	Yuli Gurriel	R	1B	605	147.3	4.91	530	59	1	1	3.4	134	0.363	0.389	0.35	3.84	13.46	17.3
49	Buster Posey	R	CA	454	112.8	3.76	395	56	0	0	4.9	140	0.379	0.431	0.361	4.94	12.35	17.28
50	Darin Ruf	R	1B	312	77	2.57	262	46	2	0	2.5	144	0.384	0.419	0.357	4.45	12.7	17.15
51	Paul Goldschmidt	R	1B	679	167.5	5.58	603	67	12	0	4.9	138	0.373	0.444	0.355	5.29	11.86	17.14
52	Mookie Betts	R	RF	550	133.5	4.45	466	68	10	5	3.9	131	0.365	0.367	0.365	3.5	13.6	17.09
53	Thairo Estrada	R	SS	132	32.5	1.08	121	9	1	0	0.9	119	0.346	0.291	0.391	1.12	15.79	16.9
54	Nicky Lopez	L	SS	565	136.5	4.55	497	49	22	1	4.4	106	0.329	0.308	0.336	3.11	13.7	16.81
55	Yoan Moncada	S	3B	616	151	5.03	520	84	3	2	4.5	122	0.347	0.314	0.358	2.5	14.28	16.78
56	Wander Franco	S	SS	308	76.3	2.54	281	24	2	1	2.5	127	0.348	0.429	0.303	4.88	11.81	16.69
57	Jonathan India	R	2B	631	150.8	5.03	532	71	12	3	3.9	122	0.363	0.361	0.363	3.49	13.17	16.67
58	Brett Phillips	L	CF	292	71.5	2.38	253	33	14	3	1.8	103	0.313	0.18	0.365	0.54	16	16.53
59	Alex Verdugo	L	LF	604	148.8	4.96	544	51	6	2	2	107	0.334	0.246	0.378	1.02	15.46	16.47
60	George Springer	R	CF	342	84	2.8	299	37	4	1	2.4	140	0.381	0.401	0.374	3.52	12.76	16.28
61	Will Smith	R	CA	501	118	3.93	414	58	3	0	4.6	130	0.364	0.307	0.385	1.73	14.42	16.15
62	Max Muncy	L	1B	592	145	4.83	497	83	2	1	4.9	140	0.379	0.406	0.369	4.22	11.9	16.13
63	Jose Ramirez	S	3B	636	156	5.2	552	72	27	4	6.3	137	0.372	0.368	0.374	3.06	13.04	16.1
64	Marcus Semien	R	2B	724	179.5	5.98	652	66	15	1	6.6	131	0.368	0.334	0.38	1.98	14.06	16.05
65	Rafael Ortega	L	CF	330	81.5	2.72	296	30	12	6	1.6	120	0.355	0.222	0.383	0.17	15.86	16.03
66	Nico Hoerner	R	2B	170	41.5	1.38	149	17	5	3	0.8	104	0.33	0.287	0.349	2.24	13.77	16.02
67	Connor Joe	R	LF	211	51.3	1.71	179	26	0	0	1.4	116	0.366	0.394	0.355	3.9	12.02	15.92
Sheet2

 

[Fluff]

Thanks for that.
Try

=LET(I,INDEX(SORT(E2:R1000,14,-1),SEQUENCE(S1),SEQUENCE(,14)),CHOOSE({1,2},AVERAGE(INDEX(I,,1)),AVERAGE(INDEX(I,,14))))

and without LET

=CHOOSE({1,2},AVERAGE(INDEX(INDEX(SORT(E2:R1000,14,-1),SEQUENCE(S1),SEQUENCE(,14)),,1)),AVERAGE(INDEX(INDEX(SORT(E2:R1000,14,-1),SEQUENCE(S1),SEQUENCE(,14)),,14)))

Cell S1 holds the number of rows to be averaged.
The LET function allows you to store the value of a calculation in a variable, so that it's only calculated once, rather than multiple times.