View Single Post
  #3   Report Post  
Old June 15th 07, 04:26 PM posted to microsoft.public.excel.misc
Duncs Duncs is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 65
Default 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 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- 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.

Also, if I can ask about the formulas...

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?


Many thanks for your help

Duncan