Average of Top 4 Ranked

[paulfitz320]

Hi, I have 3 columns... EVENT-SCORE-AVG of Top 4

I need a formula for COL C that will put the average of the TOP 4 ranked (COL B) for each EVENT in COL A

Thanks

Event Score Avg Of Top 4
1 131 129.00
1 129 129.00
1 129 129.00
1 127 129.00
1 126 129.00
1 124 129.00
1 124 129.00
1 123 129.00
1 0 129.00
2 116 101.50
2 100 101.50
2 98 101.50
2 92 101.50
2 91 101.50
2 81 101.50
2 78 101.50
2 0 101.50
2 0 101.50
3 107 53.00
3 105 53.00
3 0 53.00
3 0 53.00
3 0 53.00
3 0 53.00
4 104 79.25
4 80 79.25
4 78 79.25
4 55 79.25
4 0 79.25
4 0 79.25
4 0 79.25
5 136 125.00
5 128 125.00
5 124 125.00
5 112 125.00
5 0 125.00
5 0 125.00
5 0 125.00

 

[Eric W]

A couple of options:

	A	B	C	D
1	Event	Score	Avg of Top 4
2	1	131	129	129
3	1	129	129
4	1	129	129
5	1	127	129
6	1	126	129
7	1	124	129
8	1	124	129
9	1	123	129
10	1	0	129
11	2	116	101.5	101.5
12	2	100	101.5
13	2	98	101.5
14	2	92	101.5
15	2	91	101.5
16	2	81	101.5
17	2	78	101.5
18	2	0	101.5
19	2	0	101.5
20	3	107	53	53
21	3	105	53
22	3	0	53
23	3	0	53
24	3	0	53
25	3	0	53
26	4	104	79.25	79.25
27	4	80	79.25
28	4	78	79.25
29	4	55	79.25
30	4	0	79.25
31	4	0	79.25
32	4	0	79.25
33	5	136	125	125
34	5	128	125
35	5	124	125
36	5	112	125
37	5	0	125
38	5	0	125
39	5	0	125

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C2 =AVERAGE(INDEX($B$2:$B$100,MATCH(A2,$A$2:$A$100,0)):INDEX($B$2:$B$100,MATCH(A2,$A$2:$A$100,0)+3)) D2 =IF(A2<>A1,AVERAGE(B2:B5),"") <tbody> </tbody>

<tbody>
</tbody>

Both assume that the table is sorted as shown, with events grouped, and scores high to low.


Edit: If you don't want the gaps, the second formula can be:

=IF(A2<>A1,AVERAGE(B2:B5),C1)


And if they are not sorted:

=AVERAGE(LARGE(IF(A2=$A$2:$A$100,$B$2:$B$100),{1,2,3,4}))
with Control+Shift+Enter.

 

[paulfitz320]

Thanks Eric, very much appreciated. Does the job nicely!

 

[Eric W]

Glad to help!