Home 
Search 
Today's Posts 
#1




The Callaway Handicap System
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 / email me a template / Excel file that will do the above? I thank you in advance fo ryour help Duncs 
#2




The Callaway Handicap System
Duncs,
You could use a UserDefined 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 / email me a template / Excel file that will do the above? I thank you in advance fo ryour help Duncs 
#3




The Callaway Handicap System
On 15 Jun, 15:21, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Duncs, You could use a UserDefined 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 / email 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(D3202*C3:C20,2*C3:C20,D320))} From this, I believe it is saying... The Sum of the values in the range D320 (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 D320 (Golfer Two scores). Is this right? Many thanks for your help Duncan 
#4




The Callaway Handicap System
Duncan,
The rules of the Callaway system is that the highest score that can count against the golfer is twice the hole's par. So, if you shoot 10 on a par 4, your score is 8 for that hole  thus the formula. So, in your example, on the 15th hole, the 7 on a par 3 only counts as 6. See 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 CtrlShiftEnter. 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(D3202*C3:C20,2*C3:C20,D320))} From this, I believe it is saying... The Sum of the values in the range D320 (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 D320 (Golfer Two scores). Is this right? Many thanks for your help Duncan 
#5




The Callaway Handicap System
Bernie,
Thanks for getting back to me. What was wrong / confusing me was the calculation for the raw score. Now that this is fixed, it works perfectly. Many thanks for you help Duncs  "There are people who have money and people who are rich." ~ Coco Chanel "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Duncan, The rules of the Callaway system is that the highest score that can count against the golfer is twice the hole's par. So, if you shoot 10 on a par 4, your score is 8 for that hole  thus the formula. So, in your example, on the 15th hole, the 7 on a par 3 only counts as 6. See 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 CtrlShiftEnter. 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(D3202*C3:C20,2*C3:C20,D320))} From this, I believe it is saying... The Sum of the values in the range D320 (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 D320 (Golfer Two scores). Is this right? Many thanks for your help Duncan 
#6




xls to run callaway handicap.
Hi i have found this site while trying to down load a copy of the system can any one email me this spread sheet?
Thanks tonyob1 EggHeadCafe.com  .NET Developer Portal of Choice http://www.eggheadcafe.com 
#7




Callaway Handicap system
Hi I am looking for a copy of this would you help me out. my email is
Thanks EggHeadCafe.com  .NET Developer Portal of Choice http://www.eggheadcafe.com 
#8




Callaway Handicap system
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 email is Thanks EggHeadCafe.com  .NET Developer Portal of Choice http://www.eggheadcafe.com 
#9




The Callaway Handicap System
Bernie,
I have another problem with the spreadsheet you sent, that I hope you can help with. As mentioned in a previous post, I have the following Par's entered in Column B: 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3 I now have the following scores in Column C: 10, 8, 8, 10, 6, 10, 8, 6, 8, 8, 6, 8, 10, 8, 6, 8, 10, 6 This give a raw score of 144, and a Callaway Score of 75. However, I think this score should be 86. If I then trace through the code for the Callaway function, I find that the scores passed through are 1 less than the scores on the spreadsheet. So, it is actually using the values, in HoleScores, of 9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5. 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 CtrlShiftEnter. 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(D3202*C3:C20,2*C3:C20,D320))} From this, I believe it is saying... The Sum of the values in the range D320 (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 D320 (Golfer Two scores). Is this right? Many thanks for your help Duncan Hide quoted text   Show quoted text  
#10




The Callaway Handicap System
Duncs,
I assumed that the pattern of holes to subtract continued, so for a raw score of 144, then the 7 1/2 worst holes could be subtracted. If we limit the adjustment to 6 holes max, then the adjusted score would be 87. But I did miss the maximum adjustment of 50 strokes  the last line should be 'Output final Callaway score Callaway = Application.Max(RawScore  50, Callaway  HAdj) Let me know if the limit is really 6 holes or not... it is easy to adjust the code to account for it. HTH, Bernie MS Excel MVP "Duncs" wrote in message oups.com... Bernie, I have another problem with the spreadsheet you sent, that I hope you can help with. As mentioned in a previous post, I have the following Par's entered in Column B: 5, 4, 4, 5, 3, 5, 4, 3, 4, 4, 3, 4, 5, 4, 3, 4, 5, 3 I now have the following scores in Column C: 10, 8, 8, 10, 6, 10, 8, 6, 8, 8, 6, 8, 10, 8, 6, 8, 10, 6 This give a raw score of 144, and a Callaway Score of 75. However, I think this score should be 86. If I then trace through the code for the Callaway function, I find that the scores passed through are 1 less than the scores on the spreadsheet. So, it is actually using the values, in HoleScores, of 9, 7, 7, 9, 5, 9, 7, 5, 7, 7, 5, 7, 9, 7, 5, 7, 9, 5. 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 CtrlShiftEnter. 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(D3202*C3:C20,2*C3:C20,D320))} From this, I believe it is saying... The Sum of the values in the range D320 (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 D320 (Golfer Two scores). Is this right? Many thanks for your help Duncan Hide quoted text   Show quoted text  
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Bowling: Handicap between Teams  Excel Worksheet Functions  
golf handicap  Excel Discussion (Misc queries)  
golf handicap  Excel Discussion (Misc queries)  
excel causing system to be in low system resource  Excel Discussion (Misc queries)  
Golf Handicap  Excel Worksheet Functions 