Home 
Search 
Today's Posts 
#11




The Callaway Handicap System
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 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  Hide quoted text   Show quoted text  
#12




The Callaway Handicap System
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 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  Hide quoted text   Show quoted text  
#13




The Callaway Handicap System
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 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  Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  
#14




The Callaway Handicap System
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 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  Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  
#15




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 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  Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  Hide quoted text   Show quoted text  
#16




The Callaway Handicap System
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 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 ... read more » Hide quoted text   Show quoted text  
#17




The Callaway Handicap System
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 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 ... read more » Hide quoted text   Show quoted text  
#18




The Callaway Handicap System
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  
#19




The Callaway Handicap System
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 
#20




The Callaway Handicap System
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 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 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 