Golf

Question

Hi! I keep a handicap system for a golf league. I currently have an excel spreadsheet where I keep 10 scores and it will average the 4 highest scores and give me the total. Right know when I enter new scores I have to copy and paste the scores. This allows me to drop the oldest and add the newest score.

Is there a way I can just add the newest score to the row and the oldest will drop off. I have over 100 golfers in the league. This would be so much faster than copying and pasting. I still want to be able to keep 10 scores and average the 4 highest of the 10. Hopefully this all makes sense.

Thanks

Tim

m_tarler · Answer

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

hansvogelaar · Answer

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?

halen40 · Answer

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.&nbsp;NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Average&nbsp;Akers,&nbsp; Brian34553123545&nbsp;Alligood, Patrick2722323328193333292233&nbsp;Alliston, Bobby68679471210610&nbsp;Amos, Warren2216132316142816161122&nbsp;Badenhorst, Wessel147512&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;Barnes, Ron13181423&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17&nbsp;Bell, Shane162111131824911101220&nbsp;Bennett, Brandon3929253022333030232933&nbsp;Bickley, Phil1312201921181515&nbsp;&nbsp;20&nbsp;Bilbrey, Monty209121315918&nbsp;&nbsp;&nbsp;17&nbsp;Biscoglia, Sonny898145&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;

halen40 · Answer

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

m_tarler · Answer

As I mentioned above.&nbsp; I recommend having 2 sheets.&nbsp; Data ENTRY and then your OUTPUT sheetin 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.&nbsp; I higly recommend the date column but again even that you could drop.&nbsp; (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&nbsp;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).&nbsp; 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.

Forum Discussion

Golf

Resources