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>
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>