Remember Me?

 Duncs external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 65 The Callaway Handicap System

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

 Bernie Deitrick external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,441 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 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

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

Duncan

 Bernie Deitrick external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,441 The Callaway Handicap System

Duncan,

The rules of the Callaway system is that the highest score that can count against the golfer is
twice the hole's par. So, if you shoot 10 on a par 4, your score is 8 for that hole - thus the
formula. So, in your example, on the 15th hole, the 7 on a par 3 only counts as 6.

See

for the scoring system that I implemented.

As for the raw score, I made a mistake, forgetting to use absolute references. Instead of :

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

I should have used

=SUM(IF(C3:C202*\$B\$3:\$B\$20,2*\$B\$3:\$B\$20,C3:C20))

entered with Ctrl-Shift-Enter. Then it will work correctly when copied to the right.

Note that I didn't make that mistake in the function usage. This is correct, and can be copied.

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

Bernie
MS Excel MVP

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

Duncan

 Duncs[_2_] external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 1 The Callaway Handicap System

Bernie,

Thanks for getting back to me. What was wrong / confusing me was the
calculation for the raw score. Now that this is fixed, it works perfectly.

Many thanks for you help

Duncs

--
"There are people who have money and people who are rich."
~ Coco Chanel

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Duncan,

The rules of the Callaway system is that the highest score that can count
against the golfer is twice the hole's par. So, if you shoot 10 on a par
4, your score is 8 for that hole - thus the formula. So, in your example,
on the 15th hole, the 7 on a par 3 only counts as 6.

See

for the scoring system that I implemented.

As for the raw score, I made a mistake, forgetting to use absolute

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

I should have used

=SUM(IF(C3:C202*\$B\$3:\$B\$20,2*\$B\$3:\$B\$20,C3:C20))

entered with Ctrl-Shift-Enter. Then it will work correctly when copied to
the right.

Note that I didn't make that mistake in the function usage. This is
correct, and can be copied.

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

Bernie
MS Excel MVP

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

Duncan

 tony Burgess external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2 xls to run callaway handicap.

Hi i have found this site while trying to down load a copy of the system can any one e-mail me this spread sheet?
Thanks
tonyob1

EggHeadCafe.com - .NET Developer Portal of Choice
 tony Burgess external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2 Callaway Handicap system

Hi I am looking for a copy of this would you help me out. my e-mail is
Thanks

EggHeadCafe.com - .NET Developer Portal of Choice
 Gord Dibben external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 22,908 Callaway Handicap system

Suggest you search the 'net for "callaway scoring excel".

BTW..........the spambots now have a new customer.

Posting your email is an open invitation.

Gord Dibben MS Excel MVP

On Sun, 01 Jul 2007 18:12:33 -0700, tony Burgess wrote:

Hi I am looking for a copy of this would you help me out. my e-mail is
Thanks

EggHeadCafe.com - .NET Developer Portal of Choice

 Duncs external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 65 The Callaway Handicap System

Bernie,

I have another problem with the spreadsheet you sent, that I hope you
can help with.

As mentioned in a previous post, I have the following Par's entered in
Column B: 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3

I now have the following scores in Column C: 10, 8, 8, 10, 6, 10, 8,
6, 8, 8, 6, 8, 10, 8, 6, 8, 10, 6

This give a raw score of 144, and a Callaway Score of 75. However, I
think this score should be 86.

If I then trace through the code for the Callaway function, I find
that the scores passed through are 1 less than the scores on the
spreadsheet. So, it is actually using the values, in HoleScores, of
9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5.

Rgds

Duncs

On Jun 16, 5:17 pm, "Duncs" wrote:
Bernie,

Thanks for getting back to me. What was wrong / confusing me was the
calculation for the raw score. Now that this is fixed, it works perfectly.

Many thanks for you help

Duncs

--
"There are people who have money and people who are rich."
~ Coco Chanel

"Bernie Deitrick" <deitbe @ consumer dot org wrote in l...

Duncan,

The rules of theCallawaysystem is that the highest score that can count
against the golfer is twice the hole's par. So, if you shoot 10 on a par
4, your score is 8 for that hole - thus the formula. So, in your example,
on the 15th hole, the 7 on a par 3 only counts as 6.

See

for the scoring system that I implemented.

As for the raw score, I made a mistake, forgetting to use absolute

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

I should have used

=SUM(IF(C3:C202*\$B\$3:\$B\$20,2*\$B\$3:\$B\$20,C3:C20))

entered with Ctrl-Shift-Enter. Then it will work correctly when copied to
the right.

Note that I didn't make that mistake in the function usage. This is
correct, and can be copied.

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

Bernie
MSExcelMVP

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

Duncan- Hide quoted text -

- Show quoted text -

 Bernie Deitrick external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,441 The Callaway Handicap System

Duncs,

I assumed that the pattern of holes to subtract continued, so for a raw
score of 144, then the 7 1/2 worst holes could be subtracted. If we limit
the adjustment to 6 holes max, then the adjusted score would be 87.

But I did miss the maximum adjustment of 50 strokes - the last line should
be

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)

Let me know if the limit is really 6 holes or not... it is easy to adjust
the code to account for it.

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message
oups.com...
Bernie,

I have another problem with the spreadsheet you sent, that I hope you
can help with.

As mentioned in a previous post, I have the following Par's entered in
Column B: 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3

I now have the following scores in Column C: 10, 8, 8, 10, 6, 10, 8,
6, 8, 8, 6, 8, 10, 8, 6, 8, 10, 6

This give a raw score of 144, and a Callaway Score of 75. However, I
think this score should be 86.

If I then trace through the code for the Callaway function, I find
that the scores passed through are 1 less than the scores on the
spreadsheet. So, it is actually using the values, in HoleScores, of
9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5.

Rgds

Duncs

On Jun 16, 5:17 pm, "Duncs" wrote:
Bernie,

Thanks for getting back to me. What was wrong / confusing me was the
calculation for the raw score. Now that this is fixed, it works
perfectly.

Many thanks for you help

Duncs

--
"There are people who have money and people who are rich."
~ Coco Chanel

"Bernie Deitrick" <deitbe @ consumer dot org wrote in
l...

Duncan,

The rules of theCallawaysystem is that the highest score that can count
against the golfer is twice the hole's par. So, if you shoot 10 on a
par
4, your score is 8 for that hole - thus the formula. So, in your
example,
on the 15th hole, the 7 on a par 3 only counts as 6.

See

for the scoring system that I implemented.

As for the raw score, I made a mistake, forgetting to use absolute

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

I should have used

=SUM(IF(C3:C202*\$B\$3:\$B\$20,2*\$B\$3:\$B\$20,C3:C20))

entered with Ctrl-Shift-Enter. Then it will work correctly when copied
to
the right.

Note that I didn't make that mistake in the function usage. This is
correct, and can be copied.

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

Bernie
MSExcelMVP

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

Duncan- Hide quoted text -

- Show quoted text -

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Yan Excel Worksheet Functions 9 June 10th 07 04:11 AM neo314trinity Excel Discussion (Misc queries) 5 March 15th 06 06:46 PM Phineus Excel Discussion (Misc queries) 4 July 4th 05 03:16 AM inenewbl Excel Discussion (Misc queries) 0 April 5th 05 04:11 PM Dick Gwin Excel Worksheet Functions 3 March 2nd 05 03:07 PM

All times are GMT +1. The time now is 01:43 PM. Copyright ©2004-2019 ExcelBanter.