• 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

Hi Mark ,


The problem with using 0 for those who did not decide to participate in the Skins , is that you are finding out the lowest score for every hole , and then seeing who is the only player to get that lowest value ; if there is only one player in a hole , then he / she is entitled to the PRIZE amount for that hole. Is this correct ? If so , then instead of using 0 , we should use a high number , say 99 , so that the Skins calculation returns the correct result.


If you can upload a file which has valid data from some earlier outing , then we can put in the formulae , and see if the results match your earlier manually calculated results ; is this possible ?


Narayan
 
Are you implying that leaving a cell blank is equivalent to a 0? Then I would prefer a different approach from putting a high number like 99. Because I would print the final product as a PDF and post on our golf website. See?


I am reuploading the Revision5 file and one recent actual results per your request. I can check your reply in the morning before I take off to the golf outing.


Thank you again!


https://www.dropbox.com/s/79mk2rj8d1yikx9/MasterNetSkins_R5.xlsx


https://www.dropbox.com/s/o0to4ler07tntgs/2013-01-09
 
Hi Mark ,


I'll check out your files ; hopefully I can have something ready for you in time.


Regarding the 99 , there is no problem as far as printing the results are concerned ; with a cell format such as [<90]#; , the cell containing the 99s will not display anything , but the 99 can be used in all formulae.


Narayan
 
Hi Mark ,


Can you check this version ?


https://www.dropbox.com/s/0b1lysui5n1xe3w/MasterNetSkins_R6.xlsx


I have added a helper column to sort the list of golfers in alphabetical order ; most of the formulae and the CF formulae have been changed ; please go through the whole workbook and comment.


I have not been able to understand the SKINS part of it ; the RESULTS tab in the 23-01-09 file had one set of names , and the SKINS tab in that file has another set of names ; how can this be ? Shouldn't the list of golfers on the RESULTS tab be the complete list ?


Can you clarify how the list of names on the SKINS tab is derived ?


Narayan
 
Narayan, I am sorry for causing the confusion when you asked me for the recent results. 2013-01-09 is a spreadsheet without the indexing forumulas you have helped build. The entries there were done manually. The list of golfers for the Net Skins game is a subset of the golfers list in the RESULTS, obviously because it is an optional game thusly does not necessarily have the 100% participation.


That has to be clear with you before I could begin commenting on your R6 file. In order to understand better about your effort, I did copy and paste the results from last Saturday onto your R6 spreadsheet. I have restored my CF for the Par/Birdie/Eagle (Q8:11 in RESULTS tab) simply by copying and pasting from my version of NetSkins file. However I noticed the yellow pattern did not show up for the medalist low gross score (X19) and medalist low net score (Z15) both in RESULTS tab.


Was it intentional for you to leave two rows available at the bottom of the list of golfers range (Rows 38 & 39 in RESULTS tab and Rows 37 & 38 in the SKINS tab)? What should I do about the next three rows returning "#!REF" in the SKINS tab - deleting those rows after the final list of all participating golfers that paid for this NET SKINS game.


I am trying to find the helper column you were referring to; is $AH$!5:39 that helper column? I have entered the amount 'PAID FOR SKINS' for AC15:37. This is where you wanted for the helper column to read off and list aphabetically the paid and thus participating golfers in the SKINS tab.


Below is the data I entered from last Saturday's results in the B15:V37 for your peruse. You will notice the reverse effect with the resultant net scores of those two players who chose not to particpate and their cells are void of the amount (AC15 & AC36). I suppose this has caused the L2 in SKINS tab not reading the number of participating golfers correctly. If any of the formula(s) you improved upon in R6 was compromised, please accept my apology for my learning curve:


https://www.dropbox.com/s/w144dbbgthjsifl/MasterNetSkins_R7.xlsx
 
Hi Mark ,


No isses , I am also learning !


Let me ask you to do one thing ; since I think you have obtained the results of day before yesterday's outing manually , can you now upload the results , just as you did for the file 2013-01-09 ?


I will then put in the golfer's names and gross scores in the R6 version which I have , and compare the results the worksheets give , versus the results you have manually calculated.


I only need to know two points :


1. The golfers whose names appear in the SKINS tab , are they the ones against whose names there are entries in column AC ( Paid for Skins ) ?


2. On the SKINS tab , do you want the results of only the above golfers , or do you want the results of all golfers ?


Narayan
 
Here is what I used your R5 file to enter the results last Saturday and saved as this file:


https://www.dropbox.com/s/yrbn6n5pupkt4gq/2013-02-09.xlsx


Two points to answer you:


1. Funny that you asked me this question since you were the one who implemented the helper column in RESULTS tab (again is it AH15:39?) and I changed the amount to $5 for those who actually paid to play the Net Skins game.


2. The SKINS tab is only for those golfers that paid $5 to participate for the handicapped net score.


To recap, the input of ALL golfers is limited to the RESULTS tab. And then the input of the $5 in the AC column again in the RESULTS tab.
 
Hi Mark ,


I have downloaded your file ; give me some time to go through it , and revise the MasterNetSkins workbook.


Narayan
 
Hi Narayan,


Just to let you know my next golf outing will be Tuesday, February 26. Allow me to comment something here to see what is your thinking on the design:


Were you taking time thinking on how to begin from a master file to handle the varying number of golfers that show up at each outing? This is what I have noticed trouble with after expanding the number of rows to a certain number of golfers showing up at that outing. That insertion of new rows caused partial breaking in the linking between the input cells in RESULTS tab and the referenced cells in SKINS tab. This deemed to me needing attention in the formula-building effort on handicapping the gross scores.


Mark
 
Hi Mark ,


Sorry If I have not yet uploaded the revised version ; the problem is actually the location of the two rows for the LOWEST HOLE STROKES and the NUMBER OF SKINS.


Can we have them at a fixed row number , which will take care of the maximum number of golfers possible ? Suppose these two rows are , for example , row numbers 50 and 51 , then whenever there is a lesser number of golfers , you can always hide the extra rows.


Narayan
 
Oh, now you pointed out the culprit. Go ahead to move those two rows for the LOWEST HOLE STROKES and the NUMBER OF SKINS to Row 61 and 62.


This will give me 50 golfers maximum. Typically it will be under 30 golfers total. I suppose I have to hide the unused rows manually per each outing. Once in a blue moon, I may have the rare incidence of over 50 gambling golfers, how do I go about expanding the rows the right way?


Go ahead to upload the revised version and that would be R8, right?


I just wonder if VBA could do the same job as well but in the process of building, what about the CF issue for pattern coloring to highlight the cell(s) of winner(s)?
 
Hi Mark ,


Can you check out the RESULTS tab in the uploaded workbook ?


https://www.dropbox.com/s/bzqkvksz01zfp63/MasterNetSkins_R8.xlsx


I have not done anything on the SKINS tab ; let us first ensure that everything that needs to be done on the RESULTS tab is completed.


1. Conditional formatting in columns C , D through L , N through V , X , Z and AA.


2. Formulae in columns AD through AH.


Narayan
 
Looks good and a minor CF clean up with the color patterns for column C, also adding 6 rows more to make 56 rows total. Hence the version is R8b.


I wish I could see the List Manager and fully understand how the formulas do in Columns AD through AH. Would RANK formula be useful for the sorting of NET results on the fly in Z15:Z70?


https://www.dropbox.com/s/wtg1j30lee3rkdm/MasterNetSkins_R8b.xlsx


Onwards to the SKINS tab.
 
Hi Mark ,


Can you check this version and see what remains to be done ?


https://www.dropbox.com/s/mmbar2eph7mugcu/MasterNetSkins_R8c.xlsx


Narayan
 
What a thrill! Since we have 56 fixed rows for golfers in RESULTS tab, whereas there are 27 possible golfer entries in the SKINS tab. Would it be straightforward to expand the number of rows there without upsetting the references in the SKINS tab? What about the list of names at AB10:25?


What I would do before each outing is to enter the name of golfers based on the order of tee times. Then after tabulating the score results, I would manually sort based on net scores first. Then if there are ties, I would again manually sort based on the lowest hole strokes from 18th Hole backwards. And then lastly, add the thicker border for every other row to guide the eyes across the row easier. I suppose a macro would do all of this and how?
 
I just did the thick borders on the SKINS tab, figuring that would be alright since all of the cells there are referenced from the RESULTS tab. Here is the example:


https://www.dropbox.com/s/s71wgiovrp2rk3i/MasterNetSkins_R8d.xlsx
 
Hi Mark ,


Whatever has been done on the RESULTS tab can be done on the SKINS tab ; we can provide for a maximum of 56 golfers.


What is the list of names in AB10:AB25 ? The following 4 names do not appear in the list of golfers in the RESULTS tab :

[pre]
Code: 
ALLEN, JIM
BARISH, HAROLD
HALE, STEPHEN
MCCULLOUOGH, STEVE
[/pre]
Can you confirm that your Excel supports macros ? If it does , then we can certainly write code to do what you want.


Narayan
 
Should I expand now to 56 rows in SKINS tab to accomodate the maximum allowable in RESULTS tab and use the HIDE command as you suggested before?


Me puzzled likewise at the four names you mentioned. That is why I asked if that list is now orphaned as result or what?


I am using Excel 2008 for the Mac and I see it has macro command under Tools.
 
Hi Mark ,


I actually don't remember how these names came there in the first place ; I think you can safely delete the entire range AB10:AB25.


Yes , you can go ahead and expand the range of names and results ( formulae , of course ) on the SKINS tab.


Once you have done that , please list down what are the other requirements that you would like to be done ; we'll take them one by one , and do the coding.


In the meantime , you can test whatever has already been done with your past result files , and see whether everything matches.


Narayan
 
Narayan,


OK, I deleted by issuing the Edit> Clear > All to erase anything on AB10:AB25. Expanded the rows including formulas to 58 rows in the SKINS tab.


I will give it another test run for this Tuesday's golf outing!


Create a macro that would allow me to sort first the net results (Z15:Z70) and then to help break the ties, the V column, U column, T column, and S column. That is usually good enough unless you can tell macro to do it up to Hole 10. Never seen any ties go that far back anyway. Then after the sorting is done, apply thicker border for every other row to the end of the list of golfers.


For those with ties, can the macro execute a bold command for the font in that cell in question to help it stand out, and repeat more for those at least three-way or greater number ties?


Here is the latest minor revision that matches the number of rows between the RESULTS tab and SKINS tab and the cleaned up AB10:25:


https://www.dropbox.com/s/z84cikckwtd4886/MasterNetSkins_R8e.xlsx


Mark
 
A cosmetic modification; adding CF with color pattern for the winning golfers in SKINS tab and use font, AMERICAN TYPEWRITER since this font would stand out better if formatted bold for the winning low hole strokes in breaking a tie in the RESULTS tab.


https://www.dropbox.com/s/3c6awtqvs9dg96i/MasterNetSkins_R8f.xlsx
 
Hi Mark ,


Thanks for the updated version. When ever you get time , can you explain the tie-breaking process with an example ?


Narayan
 
Yes, Narayan.


Take a look at a 4-way tie at Net Score of 73. What I did manually is to make a three conditional sorting for Column V, U, and T for that 4-way tie scores at Row 21 to 24. At the column V, Bricoccoli and Leon leads with a tie, and Murashige and Skedsmo trails also with a tie. Go backwards to column U where Bricoccoli and Leon ties again, so carry that one more backwards. In the column T, we see a tie breaker here where Bricoccoli wins with the lower score. Murashige has the lower score thus got the tiebreaker in column U, and that finishes the sorting there for the foursomes.


Mark
 
Narayan,


I caught what could be an oversight. In testing McCullough, I realized the result came out the same in favor of Men's Hole Handicap schedule. The formulas appears not looking up for the logic test between Men's and Ladies' Hole Handicap? Q5 in the RESULTS tab is the only cell that will read Ladies' Hole Handicap schedule. Think we have a fix before tomorrow?


Here is the re-uploaded file for your fix:


https://www.dropbox.com/s/qdd464rbryyvj2w/2013-02-26.xlsx


Mark
 
Hi Mark ,


I am not able to understand what the problem is ; can you please explain ?


Does the problem persist if you fill up the RESULTS tab entry for Steve McCullogh with valid gross scores ?


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