I'd really appreciate some help from an Excel guru!
I run an already relatively complex set of spreadsheets for a 40+ group of golfers. I record their scores each week and update and print league tables throughout the year. See the attachment for a sample of the data sheet I update.
We have also introduced a handicap adjustment system which currently runs completely seperately. I'd like to now incorporate this into my current system but do not know how.
Let me explain how this needs to work.
1. At the start of the year every golfer has a handicap - a number between 0.0 and 28.0.
2. After a round of golf the player may need to have his handicap adjusted depending on his performance.
3. I'd like to automate this as much as possible
4. After a round of golf I need to adjust the players handicap as follows:(this is complicated)
a) If the player has scored more than 36 points, then for every point more than 36 he has scored I need to subtract 0.5 from his current handicap. So if a player with a handicap of 20 scores 40 points then his handicap will be reduced by (40-36)/2 = 2. So his new handicap = 18. [The MAXIMUM reduction allowed for any one round is 3 points]. Simple so far?
5. For all the players that played on that day I need to calculate the average score (easy)
6. For every player whose actual score is one point or more BELOW the average score then I need to INCREASE their handicap by 0.1 for every whole point less than the average up to a maximum of 1 point. So - a player with a handicap of 20 scores 22 points on a day when the average score is 28 points. His handicap will increase by: (28-22)*0.1 = 0.6. Note the max that can be added to the handicap is 1.0.
7. When you look at the very small data sample I have attached you can see that under each date that a round of golf is played, a players score is recorded. In an ideal world I would like to have another column next to the 'club handicap' column that is called 'new handicap' and when I enter a new score, this cell is automatically uodated. However until I have entered all the scores for that day the average cannot be calculated so the new handicap cannot be adjusted immediately - I need a 'calculate new handicaps now' button I guess!
8. This system runs throughout the year (I can send a whole year's spreadhseet if you like!) and most players have between 20 and 70 rounds of golf.
9. If what I'd ideally like is not possible then I'd be happy if the data went to another worksheet and I could obtain the handicaps from there.
I'm not sure if I have made this sufficiently clear - but ask away - I'll do all I can to clarify in exchange for some kind soul helping me sort this out!