• 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's take a closer look at McCullough's tee status. His HCP of 38 is a good test because you would be able to monitor where the credit (-3) is supposed to shift from Hole #18 to Hole #15 in the SKINS tab (even before the cells in RESULTS tab get populated with gross strokes). Watch how no effect is made when you change the tee status from N to G and back. We need to formulate to make that work.


Q5 in RESULTS tab is the cell where one would be playing from Ladies' tee boxes. Thus this does have a direct bearing to the Hole Handicap. Does that make sense now?


Mark
 
Hi Mark ,


I find that the relevant holes are the 15th and the 18th ; when the men's hole handicaps are used , the 18th hole is affected ; when the women's hole handicaps are used , the effect switches from the 18th hole to the 15th ; everything seems to be working correctly , as far as I can understand. Can you recheck ?


Narayan
 
I don't know how you did to test to verify it is working. But you did not tell me if the formula (whereever it is) is reading or not the tee status at C4 (McCullough's) in RESULTS tab?
 
Hi Mark ,


Can you check out the following golfer's result ?


MAKOWICHUK, DAVID


Please confirm whether there is a problem.


Narayan
 
The effect does not change the same for David Makowichuk whether he has the GOLD or GREEN tee status. This means the formula is not checking for the tee selection at all. Does that help you seeking for a fix?
 
Hi Mark ,


My understanding was that the women's hole handicaps are to be used when the tee status is "R" ; that is what is being checked in the following part of the formula :


IF(INDEX(List_of_Tees,MATCH(SKINS!$A10,List_of_Golfers,0))<>"R",C$9,C$8)


Can you now specify the exact nature of the tee statuses , and what checks should be done using them ?


Narayan
 
Narayan,


Ahem, I see a problem now. Because since I said in the beginning about the typical tee colors and they are Blue and White and Red. The last one is for Ladies. This golf course is atypically using different tee colors. So I have a dilemma here unless you know of a way out of this, do you? What if I use the tee color found in the scorecard and a formula is able to read that? I would also have to deal with the CF each time, too. Otherwise I have to be contend staying with the Blue-White-Red. Say you?


Mark
 
Hi Mark ,


Can we set up a table ? How many men's tee colours will there be ? How many ladies' tee colours will there be ?


Even here , there can be problems if the same letter is used both for the men and for the ladies e.g. GOLD is used for the men , and GREEN is used for the ladies.


I think you are the best person to suggest a solution !


Narayan
 
Narayan,


That is a question groping for an impossible answer, really. Since there is no real logic thence I would not recommend building a table. It really beats me why some golf courses are doing it off color.


Just a quick thought to address this predicament is to stick with the traditional B-W-R and use the pattern color as a way to connect to the tee colors at that certain outing. We shall stick to the B-W or G-R that is built into the formula here. Doublechecking here; you did already allow me to switch between G for GOLD and W for WHITE? That would be the only exception allowed for switching. I would leave that alone.


So do we move on to the macro?


Mark
 
Narayan,


The outing yesterday worked well and I am thankful. Now as I prepared for another outing this Saturday, something went wrong with the range AD38:AG70. They are not seeing any more after 21 names on the list, and I have 23 for now. Sorry to say I was unable to troubleshoot for the culprit of this problem. Can you? I am uploading this current file for your inspecton.


https://www.dropbox.com/s/7srplxx6y55tupe/2013-03-02.xlsx


Mark
 
Hi Mark ,


I don't see any problem ; if you see the formulae in the rows 38 and beyond , they are :

[pre]
Code: 
AD38 : =IF(Z38=0,999,Z38+COUNTIF($Z$15:Z38,Z38)*0.01)

AE38 : =RANK(AD38,List_of_Net_Scores,1)

AF38 : =SMALL(List_of_Ranks,ROW(A24))

AG38 : =IF(COUNTA(List_of_Golfers)>=ROWS($A$2:A25), INDEX(List_of_Golfers, MATCH(SMALL(COUNTIF(List_of_Golfers, "<"&List_of_Golfers), ROW(A24)), COUNTIF(List_of_Golfers, "<"&List_of_Golfers), 0)), "")

AH38 : =IFERROR(INDEX($AC$15:$AC$70,MATCH(AG38,List_of_Golfers,0))<>0,FALSE)
If you see the definitions of List_of_Net_Scores , and List_of_Ranks , they are :

List_of_Net_Scores : =OFFSET(RESULTS!$AD$15,,,ROWS(List_of_Golfers))

List_of_Ranks : =OFFSET(RESULTS!$AE$15,,,ROWS(List_of_Net_Scores))
[/pre]
Unless you add more names to column B on the RESULTS tab , on row 38 and beyond , and add scores to the columns D through L , and N through V , you will not be able to see results in the columns AD through AG.


In case I have not understood your problem , please explain in more detail.


Narayan
 
Hi Narayan,


What I have just observed that the last two golfers listed in the RESULTS tab would not show up in the SKINS tab no matter whether or not I populate the scores in columns D thru V and putting $5 in column AC or not. Did you see that, too? Go ahead to populate the names as you make up down the column B and will notice this pattern of last two names not showing up in the SKINS tab. Same for the column AG. I do not understand why this is happening now.


Mark
 
Hi Mark ,


Blame it on the formulae !


I have your latest uploaded version of the master file , MasterNetSkins_R8f.xlsx , and if you see the formula for the named range List_of_Golfers , you will see :


List_of_Golfers : =INDEX(Gross_Scores,,1)


And what is Gross_Scores ?


Gross_Scores : =RESULTS!$B$15:INDEX(RESULTS!$AA:$AA,MAX(15,COUNTA(RESULTS!$B:$B))+2)


What this does is look at column B to decide how many golfers there are ; the +2 is because in the master file , there are two blank rows in column B , viz. B11 and B12 ; the words HOLE and PAR are in B13 and B14.


In your results file 2013-03-02.xlsx , I find that these two words have been moved into column C , resulting in column B having 2 more blank rows !


Increase the above +2 to +4 , and everything should be OK.


All said and done , computers do exactly what we ( or their programmers ) tell them to do !


Narayan
 
Narayan,


That makes sense. I was just cleaning up the layout to make it look nice and look how I have upset the apple cart so to speak.


I have edited from +2 to +4 in cell C10 of SKINS tab and then copied & pasted to the rest of the range. It did not effect the change at all. Hmmm.


Mark
 
Hi Mark ,


I did not mention any formula in C10 !


You need to make the change in the definition of the named range Gross_Scores ; click on Formulas , Name Manager , and edit the definition of the named range Gross_Scores.


Narayan
 
Narayan,


As I mentioned before that I do not see either Formulas or Name Manager in my Excel 2008 for the Mac. This is why I find your answer puzzling until now. Is there a fix to it in different steps or do I need to upgrade to next level in Excel version?


It is bed time for me. Go ahead to answer and I will decide on your suggestion in the morning.


Mark
 
Hi Mark ,


From what I've read on the Internet , you might have a menu like Insert , Name , Define ...


Basically the menu relating to named ranges , which is what Gross_Scores , List_of_Golfers ,... are.


BTW , I came across the following resource for Excel on the Mac :


http://www.tech-archive.net/Archive/Mac/microsoft.public.mac.office.excel/


Narayan
 
Hi Narayan,


Here is what I did on the window command menu; INSERT > NAME > DEFINE. I see a list of range names yet the edit window is so small, I had to mouse scroll to force down the long command string until I arrive at the +2 in view where you wanted me to edit to +4. This works!


Thank you. But can you explain where the offset is from so I can understand its operative effect.


Mark
 
You must log in or register to reply here.
Back
Top