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(D3

202*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