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

Hi Duncs,

I'd be happy to help you with the Callaway Handicap System in Excel. The Callaway Handicap System is a method of calculating a golfer's handicap based on their performance in a single round of golf. It's a great way to level the playing field in a tournament or outing where golfers have different skill levels.

To create a Callaway Handicap System in Excel, you can follow these steps:

1. Open a new Excel workbook and create a table with the following columns: Player Name, Gross Score, Adjusted Score, and Handicap.
2. Enter the names of the players in the first column.
3. In the Gross Score column, enter the score that each player shot in the round of golf.
4. Use the following formula to calculate the Adjusted Score for each player: Adjusted Score = Gross Score - (Par + 2) * (Number of Holes Played / 18)
   For example, if a player shot a 90 on a course with a par of 72 and played all 18 holes, their Adjusted Score would be 90 - (72 + 2) * (18 / 18) = 16.
5. Enter the Adjusted Score for each player in the third column.
6. Use the following formula to calculate the Handicap for each player: Handicap = (Adjusted Score - 72) * 0.8
   For example, if a player's Adjusted Score was 16, their Handicap would be (16 - 72) * 0.8 = -44.8. In this case, the player would receive a handicap of 45 strokes.
7. Format the table as desired and save the workbook.

I hope this helps you create a Callaway Handicap System in Excel. Let me know if you have any questions or need further assistance.

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

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?


Many thanks for your help

Duncan

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?


Many thanks for your help

Duncan

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?


Many thanks for your help

Duncan

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
http://www.eggheadcafe.com

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
http://www.eggheadcafe.com

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

Or see this google thread.

http://groups.google.com/group/micro...4df1b61372a18a

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
http://www.eggheadcafe.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -

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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs



On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Duncs,

My version doesn't use those values, and I'm not sure how you think you are using those values,
either.

To check which scores are used, change the function code to include this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole


HTH,
Bernie
MS Excel MVP


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

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs



On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bernie,

My apologies for the confusion. I think I'm getting too much
caffeine! I don't know where I was getting those figures from...my
apologies.

I've looked again at your code, and tried to follow it through using
the figures in the spreadsheet.

At the point of calculating adjustments, the Raw Score value is 144,
which is right.

The Callaway Score value is also set to 144.

The NumAdj variable is set to 7.5, but I think this should be capped
at 6. So, I've altered the code to read:

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs


On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think you are using those values,
either.

To check which scores are used, change the function code to include this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

ups.com...



Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

groups.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5 + 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP



So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs


On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think you are using those values,
either.

To check which scores are used, change the function code to include this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

ups.com...



Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

groups.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bernie,

I agree, your way looks far nicer and more compact.

However, it still doesn't provide an answer to the other issues I
raised in my reply.

Can you advise? Many thanks for your help on this...bet you wish you
hadn't answered my original post?

Rgds

Duncs


On 3 Jul, 16:08, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5 + 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP





So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs

On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think you are using those values,
either.

To check which scores are used, change the function code to include this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

groups.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Bernie,

I'm really getting lost with this now!

Can I take it from the top, so that I'm clear myself in what I'm
saying?

Column B has the following values in it: 5, 4, 4, 5, 3, 5, 4, 3, 4,
4, 3, 4, 5, 4, 3, 4, 5, 3

Column C has the following values in it: 10, 8, 8, 10, 6, 10, 8, 6, 8,
8, 6, 8, 10, 8, 6, 8, 10, 6

As you will see, the scores for each hole are double the hole pars.

So, using your code we have:

Callaway = RawScore
Callaway & RawScore = 144

'Calculate Adjustments
If RawScore CoursePar Then

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5)
* 0.5 + 0.5
NumAdj = 6

'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

Out of here, Callaway = 88

'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 = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

Within here, HAdj = (Min(144 - 72 + 1, 58) Mod 5) - 2
HAdj = (Min(73, 58) Mod 5) - 2
HAdj = (58 Mod 5) - 2
HAdj = 3 - 2
HAdj = 1
End If

If RawScore CoursePar And RawScore <= CoursePar + 3 Then
If (144 72 = TRUE) AND (144 <= 75 = FALSE)
HAdj = RawScore - CoursePar - 3
End If

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)
Callaway = Application.Max(144 - 50, 88 - 1)
Callaway = Application.Max(94, 87)
Callaway = 94


However, as I said previously, I think the HAdj value should be 2, and
that the function should return the value of 88, or am I missing
something?

Help!

Duncs


On 3 Jul, 17:31, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

However, I believe this to be incorrect.
I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

The 2 is the case if you set the maximum stroke level as Par + 58 - since the raw score is higher,
then the 2 is returned (in the adjusted code)

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

No. The maximum reduction is 50, based on the rules of the Callaway system, so a raw score of 144
can only be reduced to 94.

Am I making sense?

As much as I am ;-)

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

oups.com...



Bernie,

I agree, your way looks far nicer and more compact.

However, it still doesn't provide an answer to the other issues I
raised in my reply.

Can you advise? Many thanks for your help on this...bet you wish you
hadn't answered my original post?

Rgds

Duncs

On 3 Jul, 16:08, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0..5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5 + 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP

So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs

On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think you are using those
values,
either.

To check which scores are used, change the function code to include this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
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

groups.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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

...

read more »- Hide quoted text -

- Show quoted text -

Duncs,

You're right - I forgot to add 1, as in RawScore - CoursePar + 1

For the maximum score on the table, 130 for a par of 72, that should be

130 - 72 +1, or 59

So,

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

should be

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58 + 1)) Mod 5) - 2
Or
HAdj = ((Application.Min(RawScore - CoursePar + 1, 59)) Mod 5) - 2

So it should be 88 (not 87). As to the 88 versus 94 - the part that I
original missed was

"And finally, the maximum a golfer can deduct under the Callaway System is
50 strokes."

so 144 - 50 is 94, and is the correct return in this case.

HTH,
Bernie
MS Excel MVP




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

I'm really getting lost with this now!

Can I take it from the top, so that I'm clear myself in what I'm
saying?

Column B has the following values in it: 5, 4, 4, 5, 3, 5, 4, 3, 4,
4, 3, 4, 5, 4, 3, 4, 5, 3

Column C has the following values in it: 10, 8, 8, 10, 6, 10, 8, 6, 8,
8, 6, 8, 10, 8, 6, 8, 10, 6

As you will see, the scores for each hole are double the hole pars.

So, using your code we have:

Callaway = RawScore
Callaway & RawScore = 144

'Calculate Adjustments
If RawScore CoursePar Then

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5)
* 0.5 + 0.5
NumAdj = 6

'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

Out of here, Callaway = 88

'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 = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

Within here, HAdj = (Min(144 - 72 + 1, 58) Mod 5) - 2
HAdj = (Min(73, 58) Mod 5) - 2
HAdj = (58 Mod 5) - 2
HAdj = 3 - 2
HAdj = 1
End If

If RawScore CoursePar And RawScore <= CoursePar + 3 Then
If (144 72 = TRUE) AND (144 <= 75 = FALSE)
HAdj = RawScore - CoursePar - 3
End If

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)
Callaway = Application.Max(144 - 50, 88 - 1)
Callaway = Application.Max(94, 87)
Callaway = 94


However, as I said previously, I think the HAdj value should be 2, and
that the function should return the value of 88, or am I missing
something?

Help!

Duncs


On 3 Jul, 17:31, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

However, I believe this to be incorrect.
I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

The 2 is the case if you set the maximum stroke level as Par + 58 - since
the raw score is higher,
then the 2 is returned (in the adjusted code)

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

No. The maximum reduction is 50, based on the rules of the Callaway
system, so a raw score of 144
can only be reduced to 94.

Am I making sense?

As much as I am ;-)

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

oups.com...



Bernie,

I agree, your way looks far nicer and more compact.

However, it still doesn't provide an answer to the other issues I
raised in my reply.

Can you advise? Many thanks for your help on this...bet you wish you
hadn't answered my original post?

Rgds

Duncs

On 3 Jul, 16:08, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5
+ 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP

So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs

On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think
you are using those
values,
either.

To check which scores are used, change the function code to include
this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now
have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the
same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding
of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

groups.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.

Can you advise?

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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

...

read more »- Hide quoted text -

- Show quoted text -

Bernie,

I can't thank youo enough. You've taken the time to explain, in great
detail, all the areas where I had problems.

You've persisted with me, when I have perhaps asked stupid, pointless
questions.

Many thanks for all your help

Duncs

On 3 Jul, 23:01, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

You're right - I forgot to add 1, as in RawScore - CoursePar + 1

For the maximum score on the table, 130 for a par of 72, that should be

130 - 72 +1, or 59

So,

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

should be

HAdj = ((Application.Min(RawScore - CoursePar + 1, 58 + 1)) Mod 5) - 2
Or
HAdj = ((Application.Min(RawScore - CoursePar + 1, 59)) Mod 5) - 2

So it should be 88 (not 87). As to the 88 versus 94 - the part that I
original missed was

"And finally, the maximum a golfer can deduct under the Callaway System is
50 strokes."

so 144 - 50 is 94, and is the correct return in this case.

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

ups.com...
Bernie,

I'm really getting lost with this now!

Can I take it from the top, so that I'm clear myself in what I'm
saying?

Column B has the following values in it: 5, 4, 4, 5, 3, 5, 4, 3, 4,
4, 3, 4, 5, 4, 3, 4, 5, 3

Column C has the following values in it: 10, 8, 8, 10, 6, 10, 8, 6, 8,
8, 6, 8, 10, 8, 6, 8, 10, 6

As you will see, the scores for each hole are double the hole pars.

So, using your code we have:

Callaway = RawScore
Callaway & RawScore = 144

'Calculate Adjustments
If RawScore CoursePar Then

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5)
* 0.5 + 0.5
NumAdj = 6

'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

Out of here, Callaway = 88

'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 = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2

Within here, HAdj = (Min(144 - 72 + 1, 58) Mod 5) - 2
HAdj = (Min(73, 58) Mod 5) - 2
HAdj = (58 Mod 5) - 2
HAdj = 3 - 2
HAdj = 1
End If

If RawScore CoursePar And RawScore <= CoursePar + 3 Then
If (144 72 = TRUE) AND (144 <= 75 = FALSE)
HAdj = RawScore - CoursePar - 3
End If

'Output final Callaway score
Callaway = Application.Max(RawScore - 50, Callaway - HAdj)
Callaway = Application.Max(144 - 50, 88 - 1)
Callaway = Application.Max(94, 87)
Callaway = 94

However, as I said previously, I think the HAdj value should be 2, and
that the function should return the value of 88, or am I missing
something?

Help!

Duncs

On 3 Jul, 17:31, "Bernie Deitrick" <deitbe @ consumer dot org wrote:



Duncs,

However, I believe this to be incorrect.
I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

The 2 is the case if you set the maximum stroke level as Par + 58 - since
the raw score is higher,
then the 2 is returned (in the adjusted code)

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

No. The maximum reduction is 50, based on the rules of the Callaway
system, so a raw score of 144
can only be reduced to 94.

Am I making sense?

As much as I am ;-)

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I agree, your way looks far nicer and more compact.

However, it still doesn't provide an answer to the other issues I
raised in my reply.

Can you advise? Many thanks for your help on this...bet you wish you
hadn't answered my original post?

Rgds

Duncs

On 3 Jul, 16:08, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

'Calc the number of highest holes that need to be subtracted
NumAdj = Application.Min(Int((RawScore - CoursePar + 1) / 5) * 0.5
+ 0.5, 6)

Now, NumAdj = 6

Better would be:

'Calc the number of highest holes that need to be subtracted
NumAdj = Int((Application.Min(RawScore - CoursePar + 1, 58)) / 5) * 0.5
+ 0.5

and then

'Calculate Final Handicap Adjustment
HAdj = 0
If RawScore CoursePar + 3 Then
HAdj = ((Application.Min(RawScore - CoursePar + 1, 58)) Mod 5) - 2
End If
If RawScore CoursePar And RawScore <= CoursePar + 3 Then
HAdj = RawScore - CoursePar - 3
End If

HTH,
Bernie
MS Excel MVP

So, the code should now subtract the 6 highest scores, in this case
these are 10 x 4 & 8 x 2, which equals 56.

So, the Callaway variable now holds the value 144 - 56 = 88

There is no need to use half the holes score, so it works out the
Handicap Adjustment as 1.

Finally, the function result is calculated as the Max between:

RawScore - 50 = 94 & Callaway - HAdj = 87

So, the function returns the value 94.

However, I believe this to be incorrect.

I think it is OK up until where it calculates the Handicap
Adjustment. This should, I believe, be calculated as 2.

Then, the function should return the value of 88, the Callaway - HAdj
value and not the RawScore - 50 value.

Am I making sense?

Rgds

Duncs

On 3 Jul, 12:58, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs,

My version doesn't use those values, and I'm not sure how you think
you are using those
values,
either.

To check which scores are used, change the function code to include
this:

'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)
MsgBox "On hole #" & Hole & ", the score used was " & _
Application.Min(HoleScores(Hole).Value, _
2 * HolePars(Hole).Value)
Next Hole

HTH,
Bernie
MS Excel MVP

"Duncs" wrote in message

roups.com...

Bernie,

I'm not following this!

I've made the amendment to the code as you mentioned, and I now
have a
score of 94 showing.

Nothing has changed from my previous post...the Par's are the
same,
and the hole scores are double the par's.

There is still the concern of the code in 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. Is this
correct, or is this a problem?

I'm sorry if I'm causing you grief over my lack of understanding
of
something simple!

Rgds

Duncs

On 2 Jul, 20:20, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

groups.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.

Can you advise?

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

...

read more »- Hide quoted text -

- Show quoted text -

Duncs,

You're quite welcome. I enjoyed learning about the system and helping you
to use it. You'll need to share your workbook with other golfers, too, to
spread the joy ;-)

Bernie


I can't thank youo enough. You've taken the time to explain, in great
detail, all the areas where I had problems.

You've persisted with me, when I have perhaps asked stupid, pointless
questions.

Many thanks for all your help

Duncs

Bernie

I've been using the Callaway s/s that you supplied, and all seems to
be well. However...

Golfer 1 has the following scores: 5, 7, 9, 9, 5, 8, 8, 7, 8, 8, 6, 6,
8, 6, 6, 6, 9, 8
When I total these individually, I get a total of 129. When it gets
totalled in the s/s, I get the total of 125.

The hole pars are = 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5,
3
This gives a douoble par total of = 144

As you can see, the hole score is not greater than double the par, so
the raw score should be calculated as 129 and not 125 as it is
showing.

Can you help?

Rgds

Duncs

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

http://golf.about.com/od/handicaps/l/blcallawaysys.htm

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)

Sorry about that...

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.

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:D202*C3:C20,2*C3:C20,D3:D20))}

From this, I believe it is saying...

The Sum of the values in the range D3:D20 (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:D20 (Golfer Two scores). Is this
right?

Many thanks for your help

Duncan- Hide quoted text -

- Show quoted text -

can i ask for some help,i know the thread is old but maybe you can still help me is it applicable with the handicap and rules in this link http://www.leaderboard.com/callaway.htm


i found a file in the internet named callaway_dev.xls.
in some case it is correct but not all the time,maybe it is not updated,
please help me i need to make an excel file that can compute base on the link,thanks in advance buddies...

I have created a spreadsheet using VBA that will allow to to:
1) enter courses and course info,
2) Enter players and their scores, usually at the end of the tournament,
3) Let you select which holes to Not use in calculating the Callaway Handicap, 4) Then Calculates all scores and standings, then copies the standings to a new workbook.
5) Then all results can be deleted and a new tournament can be started, but golf course info will remain.

Email me for copies