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

T

tommytolles

New Member
Joined
Apr 17, 2019
Messages
3
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>[TABLE="width: 0"]
<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>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] , align: center"]Player[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]1/1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]1/2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]1/3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]1/4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]1/5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]4/7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]4/13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=666666]#666666[/URL] , align: center"]4/14[/TD]
[TD="align: center"]4/15[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Allen, B (Gold)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Banda, J (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Brearly, C (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Brewinger, R (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]Bridges, B (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
My bad. The dates in Row 1 are white and the table should instead look like this:

Player Scores
[TABLE="class: cms_table, width: 0"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Player[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/1[/TD]
[TD="align: center"]1/2[/TD]
[TD="align: center"]1/3[/TD]
[TD="align: center"]1/4[/TD]
[TD="align: center"]1/5[/TD]
[TD="align: center"]4/7[/TD]
[TD="align: center"]4/13[/TD]
[TD="align: center"]4/14[/TD]
[TD="align: center"]4/15[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Allen, B (Gold)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Banda, J (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Brearly, C (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"]38[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Brewinger, R (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Bridges, B (White)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
ABCDEFGHIJKLMN
1Player1-Jan2-Jan3-Jan4-Jan5-Jan7-Apr13-Apr14-Apr15-AprAverage
2Allen, B (Gold)810611998.0010.00
3Banda, J (White)2717Less than 5 scoresLess than 5 scores
4Brearly, C (White)3738Less than 5 scoresLess than 5 scores
5Brewinger, R (White)28272528282726.3327.67
6Bridges, B (White)20Less than 5 scoresLess than 5 scores
Sheet
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
605
Peter_SSs
Peter_SSs
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
804
Viau1337
V

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top