Need help returning average of top 3 scores from most recent 5 at a moment in time

[tommytolles]

Hi everyone. First post here and I need some help. I'm setting up a scoring sheet for a golf course that needs to calculate what we call a "quota". Your quota is an average of your top 3 scores from your 5 most recently played.

I have the easy stuff done. A player's score is entered into a table (Player, Date, Score) for which I have created a Pivot table displaying all players' scores chronologically.


Player Scores
<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>

	A	B	C	D	E	F	G	H	I	J	K
1	Player		1/1	1/2	1/3	1/4	1/5	4/7	4/13	4/14	4/15
2	Allen, B (Gold)		8	10	6	11	9	9
3	Banda, J (White)		27	17
4	Brearly, C (White)							37		38
5	Brewinger, R (White)		28	27	25	28	28		27
6	Bridges, B (White)		20

<colgroup><col style="width: 35px"><col width="159"><col width="22"><col width="36"><col width="36"><col width="36"><col width="36"><col width="36"><col width="36"><col width="36"><col width="36"><col width="36"></colgroup><tbody>
</tbody>

I want to have another table that mirrors this one (called Quota Calculation) - and that table will show their quota on each date there are matches played. Calculating a current quota (average best 3 of the 5 most recently played matches) is simple. But I can't figure out the formula that would appear in K5 of the mirrored version of this table (showing quota calculations), for instance. That calculation would start from J5, moving left, and would need to find the best 3 scores from the first 5 it encounters, skipping blanks - and averaging those 3.

Again, the difference here is that these quota calculations will never change as new player scores are entered, because it is only calculating quotas at that moment in time. It gets tricky because of the random empty cells you will be skipping over, so I'm sure it's some sort of Array formula but I'm not smart enough to figure it out haha.

Hope I explained this adequately and someone is able to help. Thanks in advance!


<style type="text/css"><!--br {mso-data-placement:same-cell;}--></style>

 

[tommytolles]

My bad. The dates in Row 1 are white and the table should instead look like this:

Player Scores

	A	B	C	D	E	F	G	H	I	J	K
1	Player		1/1	1/2	1/3	1/4	1/5	4/7	4/13	4/14	4/15
2	Allen, B (Gold)		8	10	6	11	9	9
3	Banda, J (White)		27	17
4	Brearly, C (White)							37		38
5	Brewinger, R (White)		28	27	25	28	28		27
6	Bridges, B (White)		20

<tbody>
</tbody>

 

[AhoyNC]

Maybe something like this. I assume that the best 3 scores are the lowest scores. If your quota is based on the higher numbers then use the formula in cell N2.
Not sure what you wanted if less than 5 scores.
These are array formulas and must be entered with CTRL-SHIFT-ENTER. Then drag down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Player		1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	7-Apr	13-Apr	14-Apr	15-Apr	Average
2	Allen, B (Gold)		8	10	6	11	9	9				8.00		10.00
3	Banda, J (White)		27	17								Less than 5 scores		Less than 5 scores
4	Brearly, C (White)							37		38		Less than 5 scores		Less than 5 scores
5	Brewinger, R (White)		28	27	25	28	28		27			26.33		27.67
6	Bridges, B (White)		20									Less than 5 scores		Less than 5 scores
Sheet

 

[tommytolles]

This worked awesome - THANK YOU!!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.