Thread: The Callaway Handicap System View Single Post
#3
June 15th 07, 04:26 PM posted to microsoft.public.excel.misc
 Duncs external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 65
The Callaway Handicap System

On 15 Jun, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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 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
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

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)
Callaway = Callaway - Application.RoundUp( _
Application.WorksheetFunction.Large(UsedScores, NumAdj + 0.5) / 2, 0)
End If
End If

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
End Function

"Duncs" wrote in message

ups.com...

I've read several posts in the group, in relation to the above. I've

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

Duncs- Hide quoted text -

- Show quoted text -

Bernie,

I am extremely grateful to you. I have noticed one small problem
though, and I'd appreciate your input on this.

I have the following par's enterered in column B - 5, 4, 4, 5, 3, 5,
4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3
Along with the following scores in column C - 6, 6, 6, 10, 5, 7, 5, 4,
5, 7, 3, 6, 7, 6, 7, 6, 8, 4

These scores total, as far as I can count, 108. However, the
spreadsheet you've provided, totals it as 107.

If I copy and paste the entire column into the next clear column in
the spreadsheet, it does indeed total 108.

In the "Raw Score" row, you have the following formula in C21:

{=SUM(IF(C3:C202*B3:B20,2*B3:B20,C3:C20))}

I'm guessing, from reading the formula, that this is saying:

If the Sum of the values in the range C3:C20 (Golfer One) is greater
than the sum of double the values in the range B3:B20 (hole par's),
then the cell C21 gets the value of double B3:B20 (the hole par's)
else, it get the value of the sum of C3:C20 (the hole scores). Is
this right?

Where I am getting confused is with the formula in cell D21.

In D21, you have the formula:

{=SUM(IF(D3202*C3:C20,2*C3:C20,D320))}

From this, I believe it is saying...

The Sum of the values in the range D320 (Golfer Two) is greater than
the sum of double the values in the range C3:C20 (Golfer One), then
the cell D21 gets the value of double C3:C20 (Golfer One scores) else,
it get the value of the sum of D320 (Golfer Two scores). Is this
right?