• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to have Excel assign individual's golf handicap to hole handicap

D

deaflarch

New Member
A golfer carries his handicap and use this for the Net Skins Game in golf. There is hole handicap assigned with 1 being the most difficult to 18 being the easiest to play in a random order on a given course.


Let's say as an example, a golfer carries a 10 handicap, thus he get deduction of 1 stroke less than the par for the hole for the first lowest 10 handicap holes. This way I would build a table of golf score hole by hole with the gross total by formula and then the individual's golf handicap assigned on the following column. The top rows of the table would have the hole # 1 to 18 and the following row below would have the hole handicap assigned from the scorecard.


Then construct a second table to show the resultant net strokes in each cell hole by hole. It would be easier if there is a place here to show a sample table?


The next challenge after this is figured out is that since we have a good number of seniors who wants to play off the women's tees and that will also has a different set of hole handicap from the men's tees. How can I build an Excel spreadsheet to read either way? I do assign the tee color, Blue and White for Men's Tees and Red for Women's Tees in a column following the player's name.
 
Hi Deaflarch,


Welcome to the forums!!


Please try upload sample file and read three green sticky posts on the main page. Explain the process with an example.


Regards,
 
Thank you Fasseh. Here is the link to my Excel file on golf handicap as explained above. Hopefully I am doing this right.


https://www.dropbox.com/s/76egqytdczw9yhw/MasterNetSkins.xlsx
 
Hi ,


I downloaded your file , but am not clear on where you want the formulae ?


Can you explain which cells need to have formulae in them , and how these formulae should work ?


Narayan
 
Sure, Narayan, I can try to explain where and how.


Golfer A has a HCP of 20 and plays from white tees. Can an Excel formula read those values and go assigning -1 against the par in the order of the Men's Hole Handicap from 1 to 18 and then again from 1 thru 2 for the total HCP of 20. The second formula will read the gross strokes for each hole in the 'RESULTS' workbook and assign the net stroke difference based on the value of golfer's HCP. Example: Golfer A has 7 strokes for Hole #1 (hole HCP is 4) on 'RESULTS' workbook and Excel should show net of 6 strokes on 'SKINS' workbook. Hole #8 (Hole HCP is 2) and Hole #15 (Hole HCP is 1) should thus show 2 net strokes less than the gross strokes.


Golfer B has a HCP of 25 and plays from red tees, thus an Excel formula would test the tee values and assign the -1 for 25 times in the Hole Par values. The second formula will go to task the same as above.


I have already figured out through conditional formatting in each cell to look for the value = 1 in the Number of Skins row and assign a pattern formatting (in purple color) to highlight the cell that has the lowest stroke value in the column.


Then how could I make Excel count the total number of skins each golfer would win under the SKINS columns X10:X16. I am not sure if any a Excel formula can search for the pattern color in each cell in the range and return the total number of skins won. Right now I had to count it manually across the rows.


Ask me again if this does not make sense still.
 
Hi ,


That's too much golf in one day !


Let me put down my understanding of your explanation in Excel terms :


Golfer A has a HCP of 20 : this is available in =SKINS!B9


plays from white tees : =RESULTS!C15


1. Can an Excel formula read those values and go assigning -1 against the par in the order of the Men's Hole Handicap from 1 to 18 and then again from 1 thru 2 for the total HCP of 20 ? Yes ; where should these formulae be entered , sheet name and cell names , if possible.


2. Golfer A has 7 strokes for Hole #1 on 'RESULTS' workbook ; this is available in =RESULTS!D15 ; Hole HCP is 4 - the hole HCP for hole #1 is 10 ( available in =SKINS!C8 ) ; please clarify / confirm.


3. Excel should show net of 6 strokes on 'SKINS' workbook ; how and where ? cell address , if possible.


If you can remove these doubts , we can go ahead and see what else comes up.


Narayan
 
The remark on "too much golf in one day" gave me a good chuckle. OK.


1. We should first name the ranges for the men's and the ladies' hole handicaps to help Excel read off those values in the same SKINS workbook if that helps. Then a formula (possibly nested) can be written in Cell C10 for Golfer A to first match the value of the range whether it be men's or ladies'. Then assign the -1 values for as many cells up to match the HCP value in Cell B10. Once the formula pass the result test, then copy the formula for the rest of the Holes and then for the rows below for more golfers.


2. The Hole HCP value should be determined based on matching the value of the color in tees. That should be available if a named range is made otherwise if it is easier to use =OFFSET forumula. Note: typcially it would be Blue and White Tees for men's hole HCP and just Red for ladies' hole HCP that male seniors would use.


3. Yes, how can Excel return the net stroke value of 6 strokes in SKINS Cell C10 if it takes writing a nested formula; show me how in the shortest formula possible. It could require a different approach to the solution that I had not thought of.


Here we pause in respect to "enough golf for a day". We will deal with counting the number of skins won in SKINS Cell X10 after the above exercise is resolved. Your turn!
 
Good day deaflarch


Golf…you go to a green lawned area where the grass has been cut by someone on their hands and knees using a pair of eyebrow scissors, you take a small white ball and hit with a stick, trying to get it in to a small hole if you do get it into the small hole you take it out and try again you do this 18 times taking many many swings with the stick, if you get the small ball into the hole with one hit of the stick you jump up and down and run around shouting and whooping.....how do you do this in Arizona......basically a desert.... one gigantic bunker :)
 
Hi ,


I have still not understood much ; let me put down my understanding of the file , and you can clarify / confirm.


1. Data is entered in the following cells on the RESULTS tab :


D15:L21 , and N15:V21.


AA15:AA21


C15:C21


2. The following data on the SKINS tab also appear to be entered :


The hole handicaps for each hole : C8:K8 and M8:U8.


3. The same values for the following are present on both the RESULTS and SKINS tabs :


The par values for each hole : D14:L14 and N14:V14 on the RESULTS tab , and C7:K7 and M7:U7 on the SKINS tab.


The hole handicaps for each golfer : Y15:Y21 on the RESULTS tab , and B9:B15 on the SKINS tab.


Can you clarify / confirm each of the above ?


The first step is to eliminate entry of the same data in more than one place within a workbook ; if it is entered in one place , the same data can be used in formulae anywhere else in the workbook.


Can you say which of the above double entries should be retained as data , and which should be converted to formulae ?


Narayan
 
1. Yes, data is manually entered in the following cells on the RESULTS tab:


D14:L14 and N14:V14 is for Hole Pars taken down from the golf course scorecard where the game is played at.


C15:C21 is for the tees a golfer chose to play from. Values are based on the colors of tees; typically Blue, White, for men's and Red for ladies'


Y14:Y21 is for each golfer's current HCP. This is based on a golfer's average score; say the course par is 72 and a golfer's HCP is 20, this means 92 score average. This is the reason for playing FOR net results to make it more interesting game for golfers of varying skills.


AA15:AA21 is for total putts results by golfer


D15:L21, and N15:V21 are the gross strokes results across each golfer's row for the round


2. The following data is entered in the following cells on the SKINS tab:


C8:K8 and M8:U8 are the men's hole handicap taken down from the golf course scorecard. Hole handicap is determined by level of playing difficulty with 1 being the most difficult to 18 being the least difficult for men's golf skills.


C9:K9 and M9:U9 are the ladies' hole handicap taken down from the same scorecard. This difficulty is based on ladies' golf skills.


3. For the cells to be entered with formula in the SKINS tab:


C7:K7 and M7:U7 to read the same data on the RESULTS tab, such as RESULTS!D14 on SKINS Cell C7.


A10:B16 in SKINS tab, the golfer and his/her HCP is tricker to achieve by a formula to read the corresponding cell in RESULTS tab; the reason being is that in the final product the golfer's net results would be sorted based on lowest to highest net scores after the gross strokes are entered in the RESULTS tab. I would however keep the golfers' name in alphabetical order on the SKINS tab and remain unsorted as the final product. I am sure there are formula(s) that read and sort for me (what a doozy) otherwise I make the manual entries.


B10:K16 and M10:U16 to assign a -1 value for each hole handicap against the hole Par value and may add another one with golfer's HCP over 18 for a resultant net stroke for each hole. Assigning the -1 value would start with the lowest hole handicap which is 1 and work up as far as a golfer's HCP allows. As an example with a golfer's HCP of 7, the assigning of -1 value would stop after the 7th lowest hole handicap. I have seen some golfers carrying a HCP as high as 42. Make sure Excel is up to the looping task by stacking up to -2 or -3 as allowed in certain hole handicap calculation.


X10 to count the number of skins won across C10:U10 by Golfer A. Cells in the C10:K16 and M10:U16 range are already conditionally formatted with =IF(C18=1,C10=C17, ) for the resultant purple pattern.


This should be an improvement and ask me again if I am not clear on any of the above.


Mark
 
Hi Mark ,


Thanks for the additional information , though I think by the end of all this discussion , whether your problem is resolved or not , I will certainly be better informed about golf scoring !


Let me digest your explanations , and I'll get back to you.


Narayan
 
Hi Mark ,


Can you check out your file here ?


https://www.dropbox.com/s/9oegshb9ucqag3z/MasterNetSkins.xlsx


I have put in the first few formulae ; the cells which have formulae in them have been colored pink. Confirm that these are OK , and thereafter , we can go to the next step , which is the following :


How do we go from the gross scores entered on the RESULTS tab , to the scores which are seen on the SKINS tab ? For example , if we take GOLFER E , the gross scores on the RESULTS tab are :

[pre]
Code: 
10	5	7	5	5	5	6	5	6
5	4	4	7	6	5	5	7	7
whereas the scores on the SKINS tab are :

3	3	6	4	3	2	4	4	5
3	4	4	4	2	3	5	5	4
[/pre]
The total score of GOLFER E on the RESULTS tab is 104 - 37 = 67 , whereas the total of the above scores on the SKINS tab is 68.


Can you check this and clarify ?


Narayan
 
I am sorry for the seemingly inconsistent net strokes in the SKINS tab as a result of butchering up effort reducing from 25 golfers to 7 here for the purpose of exercise to get an answer. It seems that you are taking a close look at Golfer A to get the drift of my challenge question, so I cleaned up the net strokes for just Golfer A unless you wish for me to do the whole range of net strokes. That is a tedious chore and I know Excel has some formula to automate that and remove any possible human error in the tallying effort. Or should I?


https://www.dropbox.com/s/76egqytdczw9yhw/MasterNetSkins.xlsx
 
Hi Mark ,


I had specifically mentioned GOLFER E , but since you mention GOLFER A , let us take this case ; I am copying and pasting the following data from your latest upload :

[pre]
Code: 
Par Scores	 5  4   3   4  4  4   4   3  5  4   4  4   3  4   3   4  5  4				

Hole Handicaps	10  4  14  16  8  2  12  18  6  5  15  9  13  1  17  11  7  3				

Gross Scores	 7  4   4   5  8  4   6   4  6  6   5  6   3  6   3   6  7  5  95  20  75

Nett Scores	 5  3   3   5  4  7   3   2  4  3   3  4   2  4   5   4  7  7
[/pre]
Can you explain how the NETT scores are derived from the GROSS ?


Narayan
 
You were taking down the numbers from the older version before I cleaned up and upload the file for your peruse. I changed from 20 to 23 for this problem challenge. So the net scores are now correctly read below for Golfer A:

[pre]
Code: 
Par Scores          5   4   3   4   4   4   4   3   5   4   4   4   3   4   3   4   5   4				

Hole Handicaps     10   4  14  16   8   2  12  18   6   5  15   9  13   1  17  11   7   3				

Gross Scores        7   4   4   5   8   4   6   4   6   6   5   6   3   6   3   6   7   5   95   23   75

Hole HCP Deduct    -1  -2  -1  -1  -1  -2  -1  -1  -1  -2  -1  -1  -1  -2  -1  -1  -1  -2

Net Scores          6   2   3   4   7   2   5   3   5   4   4   5   2   4   2   5   6   3
[/pre]
 
Hi Mark ,


Now they are !


Use the backtick before and after the section where you want the alignment to be retained.


The backtick is on the ~ key ( the one to the left of the ! key ).


Narayan
 
Hi Mark ,


Let me try and understand ; there are two handicaps being discussed here , one is the golfer's HCP , and the other is the hole HCP.


The golfer's HCP is 23 ( if we take your latest figure ) ; adding all the Hole HCP deducts gives 23 , which is fine ; but how do you get the -1 and -2 values ?


Second , what role do the Hole Handicaps play ? Their values for the first few holes are 10 , 4 , 14 ,... How do we use these values ?


Narayan
 
Hi BobHC,


"...one gigantic bunker" as it seems like for Arizona in anybody's unseen imagination. LOL Back in the 1930's when the game of golf is first taking root in the Copper State, the course designer had thought how water would be unlimited drawing from deep wells and carpeted the whole course, obliterating the desert in the process. That is until around the 1970's in paying respect to the environmental awareness, Jack Nicklaus was the first to make a bold design stroke to preserve the desert by carving fairways, teeboxes and greens into lawn oases and this helps alleviating the unfolding water supply issue. I cannot recall what was the name of that golf course in Phoenix/Scottsdale. Anyways, so anytime you see a golf tournament on the Golf Channel playiing in Arizona, you can tell if the course is young or "ancient" this way by the amount of lawn coverage.
 
How do I know that you replied without refreshing this blog webpage? I wanted to reply in a timely manner. Oh, well. Thanks for explaining the power of backtick. How cool!


The golfer's HCP is used to subtract against the Gross Score to result as Net Score you see in the RESULTS tab.


To play the NET SKINS game, we would incorporate the hole handicap in the SKINS tab in order to create a Net Stroke per hole to see who if any would win and earn the SKIN for that hole. Then we also check to see if just ONE golfer won the SKIN for that hole and thus earn a share of the prize from the cash pool.


The hole handicap numbered 1 - 18 is based on the level of difficulty to play a hole; 1 being the most difficult to score a par to 18 the least. It is available on a scorecard one picks at the golf course to be played on. Now how do we determine the value of each hole handicap to arrive at Net Stroke, we would assign the -1 value for each hole handicap starting from 1 up to 18 and again from 1 up to 18 for the total number of the golfer's HCP.


We need to tell Excel to start looking first for the value 1 in the Hole Handicap array and assign -1 to the cell where Net Strokes resides in the row of GOLFER A directly below the Hole Handicap 1, then repeat the instruction for value 2 to assign for the same reason, and so on until we reached the limit which is the total value of the golfer's HCP, that is 23 for GOLFER A. So you see 23 is more than a round of 18, hence the ability to loop to start again at hole handicap 1 to stack up the deduction total (-1 + -1 = -2 in this instance) and onwards to the golfer's HCP value limit.


Mark
 
Hi Mark ,


I think I am close to getting it , but I am not sure !


Your sentence :

[pre]
Code: 
we would assign the -1 value for each hole handicap starting from 1 up to 18
and again from 1 up to 18 for the total number of the golfer's HCP.
Let us consider the case of GOLFER E , who has a HCP of 37. The hole handicaps are :

[pre][code]Hole Handicaps   10   4  14  16   8   2  12  18   6   5  15   9  13   1  17  11   7   3
His Gross Scores were :

Gross Scores     10   5   7   5   5   5   6   5   6   5   4   4   7   6   5   5   7   7  104  37  67
[/pre]
His Net Scores were :

Net Scores 3 3 6 4 3 2 4 4 5 3 4 4 4 2 3 5 5 4[/code][/pre]
Since his HCP is 37 , how would the hole deducts be distributed ?


Narayan
 
With GOLFER E's HCP of 37, we would go through first two loops and stop at Hole Handicap #1 on the third loop for the value limit. Hence the Net Scores should look like:

[pre]
Code: 
Gross Scores 10  5  7  5  5  5  6  5  6  5  4  4  7  6  5  5  7  7

Hole HCP Deduct -2 -2 -2 -2 -2 -2 -2 -2 -2 -2 -2 -2 -2 -3 -2 -2 -2 -2
=  HCP value of 37


18 + 18 + 1 (stop counting at Hole HCP 1 in which is the 14th Hole in this exercise)

Net Scores 8  3  5  3  3  3  4  3  4  3  2  2  5  3  3  3  5  5
[/pre]
We would not want to bother totalling the Net Strokes because the point is to be the only golfer to claim the lowest net stroke for the hole to win a SKIN.
 
For GOLFER F with HCP of 7 would work this way for Hole HCP deduction;

[pre]
Code: 
Hole            1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18

Hole Handicaps  10  4  14  16  8  2  12  18  6  5  15  9  13  1  17  11  7  3

Deduct           0  -1  0  0  0  -1  0  0  -1  -1  0  0  0  -1  0  0  -1  -1 = HCP value of 7
[/pre]
 
Hi Mark ,


You need to use the backtick exactly twice : Use the backtick like this :


backtick

Your section which needs to be indented

backtick


You can see I have used the backtick once before the G of Gross Scores , and once after the last deduct score of -2 ; similarly , I have used a backtick once before the N of Net Scores , and once after the last net score of 5.


Narayan
 
Thanks Narayan for setting me straight on properly using the backticks. Still they do not exactly align perfectly like you did two replies ago. Hmmm.
 
You must log in or register to reply here.
Back
Top