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
Event	Score	Avg of Top 4

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]131[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]129[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]101.5[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]101.5[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]53[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]79.25[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]79.25[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]125[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IF(A2<>A1,AVERAGE(B2:B5),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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!