• 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

Let me practice on the tip on posting a small block of data in a Post after another reading on 'Posting a sample workbook'


You wanted to know about GOLFER E and here is my try to copy and paste from Excel:

[pre]
Code: 
HOLE PAR                5  4  3  4  4  4  4  3  5  4  4  4  3  4  3  4  5  4
GROSS RESULT           10  5  7  5  5  5  6  5  6  5  4  4  7  6  5  5  7  7
RED TEE HOLE HANDICAP   4 18 10  8 14 12 16  2  6  9  7 11  15 5  1 17  3 13
DEDUCT - FIRST LOOP    -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
DEDUCT - 2ND LOOP      -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
DEDUCT - 3RD LOOP                                                -1
NET SCORE FOR SKINS     8  3  5  3  3  3  4  3  4  3  2  2  5  4  2  3  5  5
[/pre]

Here is another way to look at how Excel would assign in a loop fashion the -1 value to each cell until it hits the value of GOLFER's HCP to finish the looping job. Does this make sense?
 
Hi Mark ,


There's another reason for that ; when you copy from your Excel worksheet and paste it here , there are tabs between each cell value ; I generally remove all of these and substitute spaces instead.


Finally I think I have understood the exact procedure of assigning the Hole Deducts for each golfer ; let me think of the formulae to get this , and I'll upload the file. Give me some time.


Narayan
 
Hi Mark ,


Try this formula , to get the hole deducts for each golfer :


=INT(Golfer_Handicap/18)+IF(Hole_Handicap>MOD(Golfer_Handicap,18),0,1)


where Golfer_Handicap refers to each golfer's handicap , viz. the values 20 , 25 , 10 , 9 , 37 , 7 and 18.


Hole_Handicap refers to each hole handicap , viz. the values 10 , 4 , 14 , 16 ,...


Narayan
 
Shall this means I first name the range B10:B16 in SKINS tab as 'Golfer_Handicap' and then the range C7:U7 as the 'Men(?)_Hole_Handicap'. I suspect you prefer to do one thing at a time before we distinguish between two sets of Hole Handicaps, right?


Then type the formula you gave in C10 on SKINS tab to test the result. If good, then copy across the range of Net Scores for Skins?
 
Finally had a chance to review what you did. Wow! You did great. This version does work! Will put that to the test this Saturday at a golf outing. I noticed that you use absolute cell references and would that be a problem if I could expand the range by copying a few rows in the middle and insert the copied rows until I get right number of golfer entries.


What about logic test for men's or ladies' hole handicap using the values of tee color?


Allow me to understand the significance of those data in the range AC15:AF21 with the shaded manila yellow color.


In addressing the tied scores between two or more golfers, actually we have a tiebreaker rule called the Western rule; start the tie breaker going backwards from 18th Hole to see which golfer has the lower score in that order. In this exercise, Golfer F correctly won the tiebreaker over Golfer G. Hole 18 was a tie so we move to Hole 17 where Golfer F has 2 gross strokes less than Golfer G. Can we write an Excel formula somewhere if not in the $Z15:21 to automate this Western rule tiebreaker sorting in which does happen quite often?


Again, can Excel automate the sorting by net total score in the RESULTS tab after completing the strokes entry for a golfer? As mentioned earlier, I would prefer in SKINS tab to have the golfers sorted in alphabetical order by last name if that is possible.
 
Hi Mark ,


I think you have two problems , one big and the other one small ; the small one is getting Excel to do what you want done ; the big one is making me understand all about golf scoring !


As I understand it , what has been done so far is to use the Gross Scores , the Golfers' handicaps , and the Hole handicaps to arrive at the Net Scores ; is this correct ?


If you confirm that whatever has been done now , works well under all conditions , then we can move on to the next requirement.


You had mentioned in one of your earlier posts about row 8 having the men's hole handicaps , and row 9 having the ladies' hole handicaps ; the file that I downloaded does not seem to have the ladies' hole handicaps on row 9 ; row 9 onwards has the net scores. Can you upload the file which has these ?


We can use the data in column C on the RESULTS tab to decide which hole handicaps would have to be applied to the golfers ; however , you mention that W is to be used for the men , and R for the ladies ; what about the Gold ? Which handicap would apply to these golfers ?


I am sure that your complete requirement can be done in Excel ; whether all of it can be done using only formulae , or whether at some stage VBA will be required will be known only when we attempt to fulfill the requirement ; let us take it one step at a time.


Narayan
 
Well, what if you decided to take up on golfing after finishing this task thanks to this concept of handicapping. Hmmm.


From a quick visual inspection, the formulas you created appears to met its objective thus far. When I clear the content (names of golfers), I get pounds symbol in all the referenced cells. Should we nest in the =IF() so to return as a blank if data is cleared in the D15:V21 range. I will surely give it a test run after we tackle a few more wrinkles along the way. What about the ability to expand the rows with the absolute cell references you made?


Yes, we can refer to the value of tee color in C15:21. Typically the red tees are for ladies and seniors who does not have the vigor or strength. The white and blue (gold in this exercise) are for men.


I had learned how to do macros a long time ago but never tried to undertake VBA lessons yet. I am warmed that you gladly obige to work with me on this regardless.


You will see that I added the LADIES' HOLE HANDICAP and cleared the GOLFERS in column B so you see the resultant pounds symbol in SKINS tab.


https://www.dropbox.com/s/06a1x5q59gaos3v/MasterNetSkins_R2.xlsx
 
Hi Mark ,


Yes , you are right ; we need to wrap an IFERROR function ( available only in Excel 2007 and later ) to ensure that the error values are not displayed.


I'll go through the latest upload , and try to incorporate some of the additions that you have mentioned. Hopefully it should be ready before your Saturday outing.


Narayan
 
I have Execl 2008 for Mac so this is equivalent to Excel 2007, right? What would make it worthwhile upgrading to 2011 for Mac?
 
Hi Mark ,


I have no idea , but this post from a website says :

[pre]
Code: 
Mac Excel 2008 is more directly comparable to PC Excel 2003 than 2007.
[/pre]
You can see if the IFERROR function is available ; put the following formula in any unused cell , and see what you get :


=IFERROR(1/0,"Invalid Operation")


If you do get the display "Invalid Operation" without the quotes , then you can safely use the IFERROR function ; if it does not exist , you should get a #NAME? error.


Narayan
 
I do get "Invalid Operation". However would it be worthwhile upgrading to 2011 for Mac. What would be the biggest reason(s) if any in your view?
 
Hi Mark ,


I don't have any knowledge on this ; this link should certainly give you more information on this :


http://en.wikipedia.org/wiki/Microsoft_Office_for_Mac_2011


The key point that it makes is this :


Office 2011 requires an Intel Mac running Mac OS X 10.5.8 or higher.


Narayan
 
Hi Mark ,


I have added the ladies' hole handicaps based on the tee colors in C15:C21 on the RESULTS tab. Please verify that everything's OK.


https://www.dropbox.com/s/k6jgh1zzhdxt03c/MasterNetSkins_R2.xlsx


Narayan
 
Narayan, I added the ladies' hole handicaps in first place for you in R2 version. Did you mean it was not there after my latest file upload? And I did not see any change in any of the nested formula. Me puzzled right now since everything looked the same.


Question on conditional formatting to assign the pattern color for the PAR, BIRDIE (1 stroke below PAR), and EAGLE (2 strokes below PAR). I set up that formatting rules in D15 in RESULTS tab, how can I duplicate the fast way other than going to each cell in the range and repeat the tedious steps? And what will happen when I expand the range to include more golfers?
 
Hi Mark ,


Sorry if I have used your file to make the change , and reuploaded the file without changing its name. The formula to derive the net scores in the SKINS tab is now :


=INDEX(Gross_Scores,MATCH($A10,List_of_Golfers,0),MATCH(C$6,Hole_Numbers,0)+2)-INT(INDEX(Gross_Scores,MATCH($A10,List_of_Golfers,0),24)/18)-IF(IF(INDEX(List_of_Tees,MATCH(SKINS!$A10,List_of_Golfers,0))<>"R",C$9,C$8)>MOD(INDEX(Gross_Scores,MATCH($A10,List_of_Golfers,0),24),18),0,1)


This is the formula in C10 , and the same is copied across and down.


The earlier formula in the same area was :


=INDEX(Gross_Scores,MATCH($A9,List_of_Golfers,0),MATCH(C$6,Hole_Numbers,0)+2)-INT(INDEX(Gross_Scores,MATCH($A9,List_of_Golfers,0),24)/18)-IF(C$8>MOD(INDEX(Gross_Scores,MATCH($A9,List_of_Golfers,0),24),18),0,1)


The portion which is highlighted is the change to take care of the ladies' hole handicaps which you had added in your latest upload.


Narayan
 
Good grief, now that makes sense. I can see that works. But I do not find the named ranges, List_of_Golfers, Gross_Scores, and List_of_Tees. Can you show me where they are?


I have answered my own conditional formatting question. It is easy to copy across the range. I am reuploading the file so you can see my effort there. I am still stumped on what formula to use for the lowest gross. I tried using =MIN(X16:X21) and it would return with pattern coloring the entire range.


If you want to move to the next task, that is to count the number of skins won in X10 from the C10:U10 range. The logical test would be value of 1 = TRUE then count if this golfer actually own the lowest strokes for that hole.


This is the file I reuploaded to show color patterns for PAR, BIRDIE, and EAGLE:


https://www.dropbox.com/s/06a1x5q59gaos3v/MasterNetSkins_R2.xlsx
 
Hi Mark ,


I have downloaded your latest file , and will go through it.


The three named ranges you have mentioned are all defined in terms of the overall data range Gross_Scores ; if you click on Name Manager , and see their definitions , you will see their definitions as follows :


Gross_Scores : =RESULTS!$B$15:$AA$21


List_of_Golfers : =INDEX(Gross_Scores,,1)


List_of_Tees : =INDEX(Gross_Scores,,2)


Narayan
 
Hi Mark ,


To get the CF for the lowest gross score , use the following rule :


Format only top or bottom ranked values


Bottom 1


and use the color of your choice.


To get the number of skins for each golfer , use the following formula in X10 , and copy down :


=SUMPRODUCT((C10:U10=$C$17:$U$17)*($C$18:$U$18=1))


Narayan
 
Narayan,


I am back home and just read your last two messages; I have yet seen the Name Manager and I don't think my Excel 2008 for the Mac has that. Any other way to see the cell ranges under those Names?


And I was unable to understand nor duplicate your CF step with Bottom 1 and assign the yellow pattern in the cell for the lowest gross, and same for the lowest net and putts. I suppose VBA would be the next step to sort the rankings after all scores are entered.


I just realized we need a tweaking (if that can be done tonight before tomorrow's outing) of the List of Golfers for the Net Skins. Everyone participates for the Net Score game but this Net Skins is an optional. One idea to manage this is to insert another column and shift data to the right (ie AC15:21) in the RESULTS tab and I would enter the amount ($5) when each golfer chips in the cash. How to do the logic test filtering the most efficient way to return the list of paid golfers in the SKINS tab for the Net Skins game?
 
Hi Mark ,


I can't say anything about some of your problems , since my OS and Office versions are different from yours ; are you able to get the correct results with the files which I have uploaded so far ? If so , let me upload this version , which has the CF , and you can see if it works at your end.


https://www.dropbox.com/s/datig9vb61y8gcx/MasterNetSkins_R3.xlsx


Narayan
 
I am unable to open this version; the error message says: This workbook contains content that is not supported by Excel 2008 for Mac. <bullet> Top 10 data filters.


Now that I completed the 29 entries of golfers and their HCP for tomorrow's outing, I get a lot of broken cell references. I have uploaded the file for your peek at it.


https://www.dropbox.com/s/irr4bwl0k0a9bgy/MasterNetSkins_R4.xlsx
 
I have an idea how to deal with the golfers that do not participate in the Net Skins game. Can we do a logic test that test the cell for the data ($5) otherwise returns FALSE so the formula would not calculate the cells of those unpaid golfer(s). That is probably the fast and dirty way for tonight so I have at least a working file for tomorrow.


https://www.dropbox.com/s/irr4bwl0k0a9bgy/MasterNetSkins_R4.xlsx
 
Hi Mark ,


Can you see this version ?


https://www.dropbox.com/s/4rq62358beibuau/MasterNetSkins_R5.xlsx


I have not understood your last post ; assuming any golfer does not turn up for the outing , his / her gross scores will be 0 for every hole ; now , which cells should reflect this ? Can you specify the cell addresses which should take care of this ?


I would think that the formulae for calculating the net scores should check for whether the gross score is 0 , and if it is , then put the net score to a very high figure of , say 99 , so that that particular golfer will be last in the overall rankings.


Narayan
 
We have two sets of games; the first one is mandatory for Net Result (Z15:Z43 in RESULTS tab) where everybody will chip in $5.


The second game called Net Skins is optional and not everybody would want to participate in it. So the logic test seeks for those who chipped in $5 (AC15:43 in RESULTS tab) and returns as TRUE otherwise the FALSE would result in blank cells across the golfer's net score row (C10:U38 in SKINS tab). Where should we have this logic test in SKINS tab is for you to determine the best way. Me think it would be a nested formula in the C10:U38 cell range in the SKINS tab. How about an =ISERROR()? So this way the golfer that did not pay to participate in the Net Skin game would result as blank cells across his row.


This kind of tweaking will allow the forumula in L2 in SKINS tab to count correctly the number of participating golfers in the Net Skins game.


I finished the CF for the Z15:AA43 that you showed for the X15:X43. Thank you!


I reuploaded the file for your review:


https://www.dropbox.com/s/79mk2rj8d1yikx9/MasterNetSkins_R5.xlsx
 
You must log in or register to reply here.
Back
Top