View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick Bernie Deitrick is offline
external usenet poster
 
Posts: 5,441
Default The Callaway Handicap System

Duncs,

You could use a User-Defined Function. The code below will be in the workbook that I will send you,
used like:

=Callaway($B$3:$B$20,C3:C20,$B$1)

Where
$B$3:$B$20 has pars for each of the 18 holes
C3:C20 has the golfer's actual scores
$B$1 has the overal par for the course

The formula can be copied to the right for additional players.

HTH,
Bernie
MS Excel MVP


Option Explicit
Function Callaway(HolePars As Range, _
HoleScores As Range, _
CoursePar As Integer) As Integer

Dim RawScore As Integer
Dim Hole As Integer
Dim NumAdj As Double
Dim HAdj As Integer
Dim i As Integer
Dim UsedScores() As Integer

'Calculate Raw Score
ReDim UsedScores(1 To HoleScores.Cells.Count - 2)
RawScore = 0

'
'Store the used scores from the first 16 holes for later use
'in adjusting the returned score
For Hole = 1 To HoleScores.Cells.Count
If Hole <= HoleScores.Cells.Count - 2 Then
UsedScores(Hole) = Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
End If
RawScore = RawScore + Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

Callaway = RawScore

'Calculate Adjustments
If RawScore CoursePar Then

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((RawScore - CoursePar + 1) / 5) * 0.5 + 0.5

'Subtract the highest scores from the first 16 holes only
For i = 1 To Int(NumAdj)
Callaway = Callaway - Application.WorksheetFunction.Large(UsedScores, i)
Next i

'Possibly, use half the holes score (rounded up to a whole number)
If NumAdj < Int(NumAdj) Then
Callaway = Callaway - Application.RoundUp( _
Application.WorksheetFunction.Large(UsedScores, NumAdj + 0.5) / 2, 0)
End If
End If

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((RawScore - CoursePar + 1) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

'Output final Callaway score
Callaway = Callaway - HAdj
End Function



"Duncs" wrote in message
ups.com...
I've read several posts in the group, in relation to the above. I've
also tried, unsucessfully, to download the templates / files that
people have posted links to.

Can someone point me in the direction of / e-mail me a template /
Excel file that will do the above?

I thank you in advance fo ryour help

Duncs