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
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
Last edited by a moderator:
