Averaging golf handicaps

[albin]

Formula for averaging golf handicaps
Needed: a formula for a spread sheet to figure the following:
1) 25 guys play golf each month and the last 3 scores are used t
calculate the handicap for the next month (many men don’t play eac
month). How can I get the spreadsheet to go back 3 scores and averag
them for each man regardless of the number of months it takes to ge
the three last scores?
2) When a new man joins in mid-year, how would his first and secon
months he plays be recognized to calculate his handicap?

(The first month represents his handicap; when he plays twice th
average of the two is his handicap; then playing 3 times gives you th
handicap. I need this to only pick up the last three scores of th
floating months played.)
example is
Bob 99 95 0 89 0 92 0 0 0 0 0 0 average 93

I tried to explain this once before but didn't do such a good job.
Hope someone can help. Albi

 

[steve smallman]

To do this with a formula is beyond me, but with a little
VBA, it becomes relatively simple.

Function Handicap(scores As Range)
Dim scorearray(3) ' limit the array to 4 elements
counter = 0
scorearray(0) = 0
For Each cell In scores
If cell.Value > 1 Then 'if non score or 0 then ignore
counter = counter + 1
If counter = 4 Then counter = 1 'only last three
to count
scorearray(counter) = cell.Value
End If
Next cell

If scorearray(1) = 0 Then
Handicap = 0
Else
If scorearray(2) = 0 Then
Handicap = scorearray(1)
Else
If scorearray(3) = 0 Then
Handicap = Int((scorearray(1) + scorearray
(2)) / 2)
Else
Handicap = Int((scorearray(1) + scorearray(2)
+ scorearray(3)) / 3)
End If
End If
End If

End Function

There are neater ways to do this, but the logic here
should be reasonably clear.

Create a module in VB editor, and past the function into
the module. Use the function the same way you would sum.

Enter it into a cell, and highlight the range it refers to

Steve