Golf Simulator League - Calculating Golf Handicaps for Google Sheets

[tcmaes]

Good morning. I've only posted on this website a couple times, although I have been an admirer for many years. I am always impressed by the solutions provided by this community.

My question today relates to creating a spreadsheet that automatically calculates the handicap indexes of golfers in my golf league. The handicap index is calculated based on all past golf rounds played. I have a handy table outlining how the handicap index is calculated based on the first round played until any subsequent rounds played.

Currently, I have been using a combination of IF statements, ROUNDDOWN, AVERAGE, and SMALL functions to calculate the handicap indexes. However, as the number of golf rounds increases, this method has become increasingly complex. It is especially challenging when there are 21 or more golf rounds, as this requires calculating the average of the lowest 8 golf rounds out of the last 20 golf rounds.

For the 21+ golf rounds (orange), I have attempted to use an ArrayFormula along with SMALL, OFFSET, LARGE, and ROW functions. However, I have not been able to get it to work consistently, particularly when there are blank cells in between. Moreover, the formula does not seem to work when combined with the IF statements I created for the first 20 golf rounds (green).

If there are any experts who can help me simplify my worksheet to resolve this issue, I would be immensely grateful. Please find an example dataset below and the expected output. Here is also a link to a shared google sheet that I created with four sample datasets: Excel Help

 

[Fluff]

Ok, how about

=LET(n,{3,6,9,12,15,17,19,20},MAP(SEQUENCE(COUNTA($B$4:$B$200)),LAMBDA(a,LET(d,C3:INDEX(C3:C200,a),f,FILTER(d,d<>""),r,COUNT(f),x,query(f,"Select * offset "&max(0,r-20)),IF(r<3,"NER",ROUNDDOWN(AVERAGE(SORTN(x,XMATCH(r,n,-1))),1))))))

 

[tcmaes]

This worked perfectly. Thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[exnoob]

I came across this discussion while looking for this exact golf handicap solution.

Question though, was Fluff's solution here an add-on to the formula already present in tcmaes' Excel Help example linked above? Or was it a stand alone replacement? I have attempted to deploy this solution as a standalone formula and get nothing but 'NER' all the way down the column. I am very green when it comes to intricate Excel formulas. Perhaps I need to replace some variables or something in Fluff's formula?

I realize this discussion is old and I will ask a new question on this board if I get no response here.

Thanks in advance for any insight.

 

[Fluff]

The formula relies on cols B & C only.

 

[exnoob]

Fluff. Thank you for the quick response. And I apologize for my lack of understanding here.

When you say it relies on columns B and C, are you talking about in the example sheet that tcmaes linked? Or on a fresh sheet?

So, on a fresh sheet, my Column B should be a player's score and Column C should be this formula? Or, as in tcmaes' sheet, Column B should be week 1, week 2, etc. and Column C should be a players score and THEN Column D should be this formula?

Again, sorry for the confusion and thanks fo r all help.

 

[Fluff]

It relies on cols B & c of the sheet that tcmaes provided.