View Single Post
  #15   Report Post  
Posted to microsoft.public.excel.misc
Duncs Duncs is offline
external usenet poster
 
Posts: 65
Default The Callaway Handicap System

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 -