• 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 tabulate and return the custom results in a golf match format

Hi Narayan,


Take a look at this uploaded file.


https://www.dropbox.com/s/vzfrl40qnfazug8/2013DCMatchFormat_rev.xlsx


I have entered the golfers' names in TWO tab since that is agreed upon right now. Notice all of the golfers in LEADER tab get the one loss. Should I ignore that until the individual strokes are entered. Or the formula could be improved to wait until the strokes are entered?


Mark
 
Hi Mark ,


That's a wonderful idea ; great.


Divide and conquer in practice.


Only , when you are sure that everything is working correctly , please keep a backup of both the files ; you also need to document any changes that will need to be carried out in both files , as and when required.


Narayan
 
Hi Mark ,


Can you check your file here ?


https://www.dropbox.com/s/73vw6opqksgips1/2013DCMatchFormat_rev.xlsx


I have tied the display to the team totals in F6 and F8 ; will this work ? Will there be any match where one team's total is 0 ? If so , then we can use ( F6 + F8 ) , so that there is no display if both team totals are 0.


Narayan
 
Hi Narayan,


Sounds logical. But I ask myself what if the strokes are entered in ONE tab, will this means the problem above for TWO tab comes back to haunt me? YES, after testing by just entering the golfers names for foursomes in Group 1 in ONE tab and filling the strokes only for Hole #1, I see the same problem comes back to haunt me, In this instance, entered 4 strokes for CA and 3 strokes for AZ at Hole 1, the result shows up; 1 under ROUNDS and 1 under LOSS columns for all of the golfers on TEAM AZ, not TEAM CA.


And then I noticed there are 5 names missing under the TEAM AZ and 3 names missing under TEAM CA. There should be 24 golfers under each team. I can't seem to find the offending formula.


Mark
 
HI Narayan,


Another question here. I have two Ellis brothers on TEAM CA and Murashiges on TEAM AZ. How can those names be unique in the LEADER tab when only last name is shown? Improve the formula then to show the first names as well? If I type both the last name and first name for each ONE, TWO, and THREE tabs, would it help lessen the compute-intensive effect for this file?


Mark
 
Hi Mark ,


Regarding duplicate names , you can see that the formula to detect duplicates is looking for a comma , which is assumed to separate the first name and last name ; if you decide that there will two identical first names , then the easiest way will be to put their names in the proper order without any comma , so that their full names will appear on the Dashboard tab.


Regarding the missing names , is this problem present in your latest uploaded file ? If so , let me check. Otherwise , please upload the file which shows the problem.


Narayan
 
Hi Narayan,


Yes, changing the names to first and last name to avoid comma does the trick. But then the alphabetical order is based on first names then. I can live with that for now. Need to resolve the missing names still in the LEADER tab. The changes can be seen in the uploaded file.


Also the uploaded file shows the strokes in Hole 1 in the ONE tab and how it effect the stats in the LEADER tab for your eyes. Please resolve those.


https://www.dropbox.com/s/w3ptqjkpeoaj7rr/2013DCMatchFormat_rev2.xlsx


Mark
 
Narayan,


It is past bedime for me. So I will check in the morning what you solved for my last question with the updated file. Let's hope all wrinkles get ironed out before the tee-off this Friday.


Mark
 
Hi Mark ,


The problem of the missing names is done ; the formulae had not been copied down correctly ; at present the formulae in columns B through L have been entered till row 50 ; in future , if you plan to extend the number of golfers beyond this number , you will need to copy the formula in the range B50:L50 downwards , as far as you want.


The problem of the values appearing in the LEADER tab can be resolved if the results of the individual rounds ( in the tabs labelled ONE , TWO and THREE ) do not appear till all players in a team ( or both players in a Singles match ) have completed all 18 holes ; I have put in this condition in cell X17 and X19 on the ONE tab ; if you agree , then this can be copied down to all the other relevant cells in column X. The same logic can be applied to the results on the other two tabs. With this change , the results on the LEADER tab will not appear incorrectly.


https://www.dropbox.com/s/hm383fsdxubb8tc/2013DCMatchFormat_rev2.xlsx


Narayan
 
Hi Narayan,


If you are still up, I have this feedback for you. I have copied the fomula in cell X17 and X19 for all of the groups in the ONE tab:


Take a look at this uploaded file: https://www.dropbox.com/s/w3ptqjkpeoaj7rr/2013DCMatchFormat_rev2.xlsx


It seems we make progress with the resolution of values appearing in the LEADER tab, but take a look at the Group 1 results. The tabulation shows two CA golfers getting the win but no result for the losing two AZ golfers. Also all of the CA golfers have now that one loss.


In reference to the first paragraph in your last message, I do not see any formula past B39:L39. Me puzzled. I will be leaving home in an hour and half if that helps with the timing of your reply. Otherwise I will look for yours later this evening.


Mark
 
Hi Mark ,


I was referring to formulae in the Players tab. The fact that the formulae had not been copied till row 50 was the reason some players' names were missing on the LEADER tab.


Regarding the values appearing on the LEADER tab , let me see how we can ensure that any player's results appear only if that particular player has completed his 18 holes in any one round.


This really should not make a difference once all the results are in ; they may give wrong results when no data entry has been done , but when all the data has been put in , do you still see any problem ?


Narayan
 
Hi Narayan,


Yes, the 2 AZ golfers in Group 1, ONE tab did not register the loss whereas the 2 CA golfers did get their win. Don't you agree that is the issue here? Otherwise I can conur with you to let all the data put in for the accurate results.


Mark
 
Hi Mark ,


When all the results are in !


We started on the presence of losses when no data had been entered , which is why we changed the condition in the IF statement ; if you can manage with erroneous results when all of the data has not been entered , then there should not be any problem.


I had posted this earlier :

[pre]
Code: 
I have tied the display to the team totals in F6 and F8 ; will this work ? Will there be any match
where one team's total is 0 ? If so , then we can use ( F6 + F8 ) , so that there is no display if
both team totals are 0.
[/pre]
If you feel that both team players' results should be displayed even when one team has scored , use an OR ; if either $F$6 <> 0 or $F$8 <> 0 , display the results , that's all.


Narayan
 
Hi Mark ,


Can you check this revision ?


https://www.dropbox.com/s/rjy7kcigfm3nwbn/2013DCMatchFormat_rev3.xlsx


Can you check the Singles tab ( THREE ) in more detail , since I have changed the definition of the named range Singles_List to start from row 16 instead of row 17 ; it is possible that the results on the Leader tab pertaining to the matches on this tab may not be getting retrieved correctly ; as and when you let me know , I'll correct it.


Narayan
 
Hi Narayan,


There will never be a match that ends with a 0. So the F6 + F8 should work neatly.


I have tested the THREE tab, filling Group 1A and 1B with complete strokes and the LEADER tab showed tabulations correctly thus far. The real test will be this Sunday.


Will let you know how this weekend goes for the formulas.


Thank you!


Mark
 
Hi Mark ,


However , the formulae which are used at present do not make use of either F6 or F8 on the LEADER tab ; the logic I have used for deciding on the rounds played is to see whether the IN total is greater than 0 ; if it is , then the round has been played by the golfer. The consequence is that each golfer's result on the LEADER tab will appear only when he has played a round in any of the 3 formats.


For this , I have introduced the helper formulae on the LEADER tab in columns AM onwards.


To check the correctness of the formulae , please check any intermediate group , say GROUP 5A and GROUP 5B , and check with both teams winning , as well as for an "AS" result.


Narayan
 
Hi Narayan,


I am back from the event. There are a few wrinkles to iron out in both MatchFormat and NetSkins files. Let me start first with this quick issue in the uploaded NetSkins.


https://www.dropbox.com/s/0i2qqpdxelc34ag/NetSkins.xlsx


It seems the table is corrupted again. I have five golfers not paying $5 seen under AC column in the RESULTS tab yet show up with net scores on SKINS tab. They are: Jose Sermeno, Nick Comegna, Richard Herzog, Wayne Miller and WIlliam Tonigan. Those who paid but did not have the net scores are: Mike Finneran, Ron Rhodes and Steve Gutierrez.


Also I cannot get to break the referenced links in NetSkins to that 2013DCMatchFormat file. Is this what is causing the corruption?


Help me resolve this issue so I can publish the results and pay the winners.


Mark
 
Hi Narayan,


Take a look at the uploaded file for Desert Cup 2013:


https://www.dropbox.com/s/7udramsy9g98q5y/2013DC.xlsx


Take a look at the LEADER tab and you will notice the number of rounds for TEAM CA players does not add up correctly. They all should show '3' under the AD column.


Mark
 
Hi Mark ,


I think you need to have a macro to create a complete workbook from a template each time you have an outing , otherwise human errors are bound to happen.


If you see the formulae in column C on the SKINS tab , everything goes well till C36 , but thereafter , there's a mixup. Can you check ?


Narayan
 
Hi Narayan,


I understand the approach to troubleshooting for the problem that begins alphabetically with the first offending row and that begins with Jose Sermeno. Then, I do catch a mixup starting at C36 based on the pattern of formula above in Column C. Shoot, this is strange to think of human error as a culprit because the ONLY cells I inputted in the SKINS tab are the hole handicaps for Men and Women. The rest was left alone. To fix it, do I simply select all the cells that has formula in Row 36 and drag to copy down to the bottom of the range?


In taking up your suggestion, how do we create a macro for this?


Lastly, if you noticed how I hacked the solution on the spot for golfers' handicap in Column Y of RESULTS tab by editing each cell there. Up to this event, all I need to do is this; in order to play Net Skins is by filling the Column Y with golfer's HCP otherwise leave it blank to get the Gross Skins result. Simple as that. At this event, I was asked to do the Half Net Skins. Holy Macro, I did not know what to do other than editing the value in each cell to halve the hard way like this '=(golfer's HCP)/2'. Is there a better way with a helper formula and not compromising the Net Score Results?


Mark
 
Hi Narayan,


Here is my thought on addressing the golfers handicap for Net Skins result. How about naming a cell, X8, 'Handicapper', and then add a formula to look at a golfer's handicap value and produces by a multiplier value to arrive at either whole or halved product. BUT I see the halved and rounddowned golfer's handicap did not effect the targeted net strokes in the range in the SKINS tab.


Example: cell B10 in SKINS tab, enter this formula =PRODUCT((IFERROR(VLOOKUP($A10,Gross_Scores,24,FALSE),99)*Handicapper)


Here is the uploaded file to check:


https://www.dropbox.com/s/s6yronhlx5m96g5/NetSkins_rev1.xlsx


Mark
 
Hi Mark ,


Please understand that what ever formulae I have written are all based on what I have understood after you have explained the subject to me.


We have defined a named range called Golfer_Handicaps , which refers to =INDEX(Gross_Scores,,24) , meaning the values in column Y on the RESULTS tab. These are used in the formulae which derive the net strokes from the gross.


If you are now introducing what you call halved and rounded down handicaps , can you please explain this to me , and clearly show how and where these are to be used ? Just indicate what the half handicap should be if the handicap is an even number , and what it should be if the handicap is an odd number. Examples are 6 , 22 , 11 , 29. What would the half handicaps be in each of these cases ?


Narayan
 
Hi Narayan,


Yes, we have understood each other and achieved for the objective of taking the gross score to arrive at net scores just fine. That design worked fine for our purpose. That is until this Desert Cup event when I heard for the first time about halving the golfer's handicap as a way to offer the better golfers a chance to win a skin or two whereas the high handicapper would tend to grab up a number of skins. This led me to this current search on how to halve a golfers' handicap on the fly to effect the desired net strokes in the range.


Keep in mind in answering your remark on Golfer_Handicaps, it is not possible taking a look through the small window in my Excel 2008 for the named cells you created in this workbook. This is why I am going to find out if Excel 2012 for Mac has a List Manager that you were telling me about, and a valid reason to upgrade right there.


Now the values in column Y on the RESULTS tab is still a good thing. What is the best way to halve the values on the fly to derive the desired net strokes from the gross???


Example: Abe Prioleau has a 25 handicap as seen in cell Y20 of RESULTS tab. Here come an event that stipulates for a half net skin, where I thought up an idea by a named cell X8 in SKINS tab where I can edit the default 1 to a 0.5 as a multiplier value. Will that work with a =PRODUCT() formula to halve the HCP? This net effect would be halving Abe's 25 HCP to 12 (rounding 12.5 to the lower whole number of 12) so that Excel will limit to assigning -1 for only first 12 holes in the net scores range, a big difference from assigning -1 for 18 holes and then loop again for the first 7 holes. Maybe you have a different and yet better idea? I think you get the drift here and fix the affecting formula(s).


Time to hit the sack and see your fix in the morning. Thank you!


Mark
 
Hi Narayan,


Waiting for your reply the past two days afford me some further thinking. Would like to resolve quickly a couple of separate issues on this posting:


Let's begin first with the error in the LEADER tab. Team California players has incorrect totals for rounds played. I see the problem where the value shows as 0 for certain cells in column AR but cannot pinpoint where the culprit is. Can you check this?


Another thing I want to comment here for this file is I like the way the formula (that remained as original) in column X handles the result of each game in THREE tab. In reality, we do find no reason to complete the game after the match is determined. Thus I want to be allowed to leave the holes unfilled beyond the point the match is concluded. The newer formula in ONE and TWO tabs would not allow me to do that. Can I go back and copy the formula in THREE tab and edit those formulas in ONE and TWO tabs without upsetting the references?


The uploaded file for the above is: https://www.dropbox.com/s/7udramsy9g98q5y/2013DC.xlsx


The next issue is I want to introduce in the Net Skins game the ability to factor (for lack of a better word) the golfer's handicap. This was explained already in an earlier posit. Further thought was put into this idea and I have already begun the process for you to complete. I have inserted a column (column AE) to move the indexing table to the right to make room for the golfer's factored handicap. Everything else is the same as before. I have named a cell AE12 for lack of better word, 'Netfactor'. Right now, the value is 0.5 (that is for halving the golfer's HCP) The formula is now populated in column AE and example in AE15 is =ROUNDDOWN(PRODUCT(Y15,Netfactor),0).


I ask you to help move the defined range reference from column Y to column AE in RESULTS tab in order to produce the desired results in NETSKINS tab. Would that do the trick I wanted, right? This means I could then type the 1 value in AE12 to get the original Net Skins or 0 to get the Gross Skins. That would be great to be able to do that kind of flexibility.


Here is the uploaded file for the Net Skins issue:


https://www.dropbox.com/s/s6yronhlx5m96g5/NetSkins_rev1.xlsx


Mark
 
Hi Mark ,


Can you download the revised file ? The first problem is resolved.


https://www.dropbox.com/s/npewkulnwttmes2/2013DC_Revised.xlsx


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