I have a spread sheet that calculates our golf league handicaps. The formula below works fine as long as everyone shows up each week. Unfortunately that does not happen and I have to keep moving the scores up from previous weeks. Here is what I need it to do.
1. I will always have 6 scores to start with. The formula needs to pick the 3 lowest of the last 6 scores.
2. Add all 3 lowest scores
3. Divide by 3
4. Subtract 36
5. Multiply by .96
6. The number is rounded to the nearest whole number
7. I also need to have a maximum handicap of 18 included.
B4-G4 are the scores from last year that are used to determine the handicap to start out the year H4
The red numbers are their weekly scores.
Examples: B4-G4 give the handicap in H4
The 3 lowest scores in E4-G4, I4, M4 and Q4 give the handicap in S4
League.jpg
I would imagine there is a way to have the formula keep counting back cells to look for an entry but that is beyond my skill set. I can always add columns between B4 and G4 for this purpose.
Thanks for your help, John
Bookmarks