Update Golf Handicaps According To Rules

Ashbo · Dec 1st 2007

Hi

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!

best regards
Tony (Ashbo)

shg · Dec 1st 2007

Re: Update Golf Handicaps According To Rules

Can you post a much larger sample of data by zipping?

Quote

a) If the player has scored more than 36 points,

Points are total strokes in some number of rounds?

Is this US or UK handicapping?

Ashbo · Dec 1st 2007

Re: Update Golf Handicaps According To Rules

Hi shg

See attached zip file - which has not been modified to show players handicaps. But you will see how this works. All updates are made to the data worksheet. I then use the other worksheets to manipulate and sort the data by hand to produce the league tables. (In fact a guy who is just about Excel illiterate does this so I have printed out a step-by -step guide for him to produce the tables).

The data files contain no handicap information at the moment - but they do contain and are updated with the scores (twice a week) and it these updates (scores) that need to be used to recalculate the players handicaps. All players have a handicap from the club at the start of the year - for our competition we take these handicaps and adjust them twice weekly to reflect players current form and performance. So each time a player plays his handicap could change. In essence if a player scores between about 29 and 36 pts his handicap will remain unchanged - scores higher or lower than this will generally either increase or decreae their handicap.

To your question -
No, points are Stableford points which is a legitmate golf scoring system. Basically if you played to your handicap - whatever it is you should get 36 points. If you play better than your handicap you will get more than 36 points and if you play worse you get less. If look at the data in the zip file you will see the ranges. Most players, in most rounds, score below their handicap - i.e. score fewer than 36 points. we only want to cut people's handicaps if they score above 36 and increase their hanidcap if they are scoring below the average for the round. (which will normally be between 28 and 31).

Hope this helps- and thanks for even considering helping me out !

Tony

shg · Dec 1st 2007

Re: Update Golf Handicaps According To Rules

Look beneath the entry data on the Data tab. The opening handicaps are random.

The handicap adjustment function is pretty brainless (maybe too brainless!):

Code

Function HcpAdj(vScore As Variant, vAvg As Variant) As Double
    Dim dFac1 As Double
    Dim dFac2 As Double
    
    If IsEmpty(vScore) Then Exit Function
    
    ' decrease hcp if score > 36
    If vScore > 36 Then
        dFac1 = -(vScore - 36) / 2
        If dFac1 < -3 Then dFac1 = -3
    End If
    
    ' increase hcp if score worse than average
    If vScore < vAvg - 1 Then
        dFac2 = Int(vAvg - vScore) / 10
        If dFac2 > 1 Then dhcp = 1
    End If
    
    HcpAdj = dFac1 + dFac2
End Function

Display More

Barryj · Dec 2nd 2007

Re: Update Golf Handicaps According To Rules

Try this if it works for you, it adjusts handicapps when score is put in cell D or I, it works with both stroke and stableford, just put their handicapp in cell B & C and when their next score is added it will adjust the actual handicapp and either increase or decrease depending on their score.

Ashbo · Dec 2nd 2007

Re: Update Golf Handicaps According To Rules

Hi shg

Thanks for your help so far - I think I see what you have done - basically defined a new function which is then applied.

I opened your attached file and again I can see what you have done or intended to do, but all the handicap stuff has not worked - for the Hcp Adj and Curr Hcap columns it says '#Name?' so I guess it is waiting for me to define the function ? If so how do I input /use the definition you kindly provided?

Maybe my Excel skills are not up to this - please explain it to me as if I were a dummy!

Thanks again
Tony

Barryj · Dec 2nd 2007

Re: Update Golf Handicaps According To Rules

Ok, in cell D6 if you type in 72 then the handicapps will be adjusted for stroke event, if you type in 36 then you are playing stableford.

Type the the players name in the cells in column A & F the put the handicapp in say 25 in column B & C and G & H, then the score for that round in column D & I if it is greater than cell D6 then it will give .1, if it is less it will take the appropiate handicapp away according to the table on the top left, this works for up to 36 handicapp, it can be adjusted if you want it to be less.

Hope this helps explain it a little.

Ashbo · Dec 2nd 2007

Re: Update Golf Handicaps According To Rules

Thanks Barryj but your reply refers to the attachment posted you Barryj which I haven't had a chance to look at yet! I was referring to the attachment posted by shg ....
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hey shg - I got it to work!

First time I opened your zip file I forgot to enable the macros - hence the problem with the data in the cells.

OK - thanks a lot I'm going to check what you did further but it looks good to me. Is there a way of saving this function permanently other than via the enable macros route?

Thanks again
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]shg - now I have examined the sample spreadsheet that you sent me it looks fine EXCEPT that in a few cases it seems to have added more than 1.0 to the Hcap - so I wonder whether the rules in the function for limiting the maximum points gained to 1.0 is working?

For example look at cells V30 and W30 - surely these should read 1.0 ?

Thanks again
Tony[hr]*[/hr] Auto Merged Post;[dl]*[/dl]shg

OK - I spotted the VBA error and edited it - Tony

shg · Dec 3rd 2007

Re: Update Golf Handicaps According To Rules

Ashbo,

Sorry for the bug, good job on finding and fixing it. For anyone else who cares:

Code

Function HcpAdj(vScore As Variant, vAvg As Variant) As Double
    Dim dFac1 As Double
    Dim dFac2 As Double
    
    If IsEmpty(vScore) Then Exit Function
    
    ' decrease hcp if score > 36
    If vScore > 36 Then
        dFac1 = -(vScore - 36) / 2
        If dFac1 < -3 Then dFac1 = -3
    End If
    
    ' increase hcp if score worse than average
    If vScore < vAvg - 1 Then
        dFac2 = Int(vAvg - vScore) / 10
        If dFac2 > 1 Then dFac2 = 1
    End If
    
    HcpAdj = dFac1 + dFac2
End Function

Display More

If you want to get rid of the UDF, then you can use this formula instead:

[COLOR="Blue"]=IF( AND(Score <> "", Avg <> ""), (Score > 36) * MAX(-3, (36 - Score) / 2) + (Score < Avg - 1) * MIN(1, INT(Avg - Score) / 10 ), 0)[/COLOR]

... replacing Score and Avg with cell references.

Ashbo · Dec 3rd 2007

Re: Update Golf Handicaps According To Rules

shg,

Nice one - I'm actually happier using a formula like this than the VBA UDF - even though I miraculously managed to spot the error!

Now - as for your formula it needs a couple of tweaks. Firstly - if there IS an average score that day but the player did not himself return a score (i.e. he did not play) - then the formula currently returns a value of 1.0 which means the player's handicap goes up by 1 and he didn't play! (Must be how it treats a 0 or blank score). So this needs to be fixed.

Secondly if there was no average score that day (e.g. suppose nobody played because the weather was too bad (not a problem in Texas I guess but in the UK we get stuff like snow and rain!)) then the formula returns an an error - well actually it returns '#VALUE!'.

Any chance you can adjust the formula to deal with these two eventualities?

Thanks again...

Ashbo

shg · Dec 3rd 2007

Re: Update Golf Handicaps According To Rules

You must have snagged the formula in my last post before I edited it, since the new one shows neither of those problems. The [COLOR="Blue"]=IF( AND(Score <> "", Avg <> ""), [/COLOR] part was added.

Ashbo · Dec 4th 2007

Re: Update Golf Handicaps According To Rules

shg

You are right I did grab the formula before you made that adjustment - which corrects both the problems I indentified earlier.

This works fine now - and I really appreciate your help.

The last thing I want to do now is that for most players, most of the time, they score abovce the average but below 36 - so the formula, correctly, returns the adjustment as 0.00

Is it possible to adjust the formula such that when the returned value is 0.00 nothing is placed in the cell. This would mean that only adjustments (both +ve and -ve) would be visible when inspecting the spreadsheet - making it easier to see what has happened.?

In any case - thanks again for your help

Ashbo[hr]*[/hr] Auto Merged Post;[dl]*[/dl]OK - doh! I just learned how to supress zero values!! Sorry to bother you.!

I think we are done - but don't worry I'll come back if there is a problem!

You are a top guy for helping me out

Cheers
Ashbo

shg · Dec 4th 2007

Re: Update Golf Handicaps According To Rules

You're welcome, Ashbo.

Update Golf Handicaps According To Rules

Participate now!

Share