Forum Discussion
Golf
Check out the TAKE function
If you need more help:
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
This is a sample of what my spreadsheet looks like. I don’t know much at all how excel works. The far left number is the oldest. Would like for it to drop off when I add a new score on the far right.
NAME
Average
Akers, Brian
3
4
5
5
3
1
2
3
5
4
5
Alligood, Patrick
27
22
32
33
28
19
33
33
29
22
33
Alliston, Bobby
6
8
6
7
9
4
7
12
10
6
10
Amos, Warren
22
16
13
23
16
14
28
16
16
11
22
Badenhorst, Wessel
14
7
5
12
10
Barnes, Ron
13
18
14
23
17
Bell, Shane
16
21
11
13
18
24
9
11
10
12
20
Bennett, Brandon
39
29
25
30
22
33
30
30
23
29
33
Bickley, Phil
13
12
20
19
21
18
15
15
20
Bilbrey, Monty
20
9
12
13
15
9
18
17
Biscoglia, Sonny
8
9
8
14
5
10
I would further suggest the sheet set up is probably not optimal.
I would suggest 1 sheet/table to have data entry like golfer, score and maybe some other info might be helpful like date, course, weather, ???
Then another sheet could have the summary information like:
List of golfers can be:
UNIQUE( [golfer name column] )
then you can have a list of the top 10 scores be:
=TRANSPOSE( TAKE( SORT( FILTER( [scores], [golfer] = golfer name ) ), 10))
and then the average of the top 4 is simply something like =AVERAGE(B2:E2)
Some of the formulas could be made more versatile (automatically spill) but in concept see the attached
You're a genius. Thanks for sharing.
Thanks. I have very limited knowledge of excel. My spreadsheet will automatically take the 4 highest scores and average them. When I post a new score I have to copy and paste in order to add a new score and drop the oldest score. I’m hoping there is a way I could just add a score and the oldest would drop off. Trying to avoid coping and pasting. That is a sample of what it looks like.
NAME
Average
Akers, Brian
3
4
5
5
3
1
2
3
5
4
5
Alligood, Patrick
27
22
32
33
28
19
33
33
29
22
33
Alliston, Bobby
6
8
6
7
9
4
7
12
10
6
10
Amos, Warren
22
16
13
23
16
14
28
16
16
11
22
Badenhorst, Wessel
14
7
5
12
10
Barnes, Ron
13
18
14
23
17
Bell, Shane
16
21
11
13
18
24
9
11
10
12
20
Bennett, Brandon
39
29
25
30
22
33
30
30
23
29
33
Bickley, Phil
13
12
20
19
21
18
15
15
20
Bilbrey, Monty
20
9
12
13
15
9
18
17
Biscoglia, Sonny
8
9
8
14
5
10
As I mentioned above. I recommend having 2 sheets. Data ENTRY and then your OUTPUT sheet
in the attached there is a data ENTRY sheet that looks like this:
I added a few extra things like course and weather because I find often later on you sometimes wish you had extra info but you could ignore or delete those columns. I higly recommend the date column but again even that you could drop. (FYI - clicking CTRL-; will insert todays date)
Then I updated the 'Summary' tab to take the most RECENT (i.e. the last 10 in the list) and show them, and then take an average of the highest 4
I ust updated the attached so the list of golfers on the Summary tab is used for data validation on the data tab (i.e. you are sure to always enter the name correctly on the data tab and gives you a drop down to make it easier). If you need to add a new golfer you just add to the list/table on the summary tab and copy the corresponding formulas down.
