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
- 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:C20>2*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(D3
20>2*C3:C20,2*C3:C20,D3
20))}
From this, I believe it is saying...
The Sum of the values in the range D3
20 (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 D3
20 (Golfer Two scores). Is this
right?
Many thanks for your help
Duncan
