• 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 Mark ,


I have been following this topic with some interest , and would like to know the following :


1. Suppose we have the scores for each hole from two teams , can we not use just :


=SIGN(Team1-Team2)


for each hole ?


This function returns 0 if both the scores are the same , +1 if Team1 takes a greater number of strokes than Team2 , and -1 if Team1 takes a lesser number of strokes than Team2.


2. Now we can just sum this result for all the 18 holes , column by column. The formula to be used is : =ABS(SUM($C$18:C18)) where column C has the starting hole , and column T has the 18th hole score. Since one end of the above range is anchored , after two holes , we will have the resultant of the first two holes , after the first three , we will have the resultant of the first three and so on.


3. We also see how many holes are remaining , by using : =COLUMNS(D19:$T$19) , where the end point is anchored , and we start counting from hole #2.


4. Whenever the result in step 2 is greater than the result from step 3 , the game is over.


Narayan
 
Hi Faseeh,


I took a peek at your Golf_Score file and read your statement in the callout. Allow me to rearrange what you typed out.


The correct way is: Whenever the difference in 1 UPs up till and including Hole 17 is greater than or equal to 2, the cell should display "x and x". If it is zero, the cell should display "AS". If it is 1, the cell should display "1 UP". If it is 2, the cell should display "2 UP".


That is great to say this way in Excel terms.


Let me add; can we make the formula return TRUE only on the winning team side of the match game? Likewise the losing side would display blank cell.


Mark
 
Hi Narayan,


What you said sounds so simple in formula building and intriguing. But keep in mind, the lower strokes gives the team/player an edge, so it/he should gain 1 UP rather than 1 DN (Down). Thus the scoring should read the opposite, increasing its total 1 UPs towards victory. Then the game is decided if the total carried is greater than the remaining holes to play. That part is already explained in the previous posting and above. Can you try building a sample sheet to demonstrate your thinking if you follow the previous postings.


Mark
 
Hi deaflarch,


Please check this one:


http://dl.dropbox.com/u/60644346/Golf_Score%20(dtd%2003.03.2013).xlsx


Cell X13 & X14


Faseeh
 
Hi Mark ,


Check out the file here , which is Faseeh's file where I have added the formulae I had posted earlier.


https://www.dropbox.com/s/pxdl93i3tv9qsxe/Golf_Score%20%28dtd%2003.03.2013%29_TRIAL.xlsx


Narayan
 
Hi Narayan and Fasseh,


I have edited the hole strokes to give the Golf_Score (dtd 03.03.2013)_TRAIL.xlsx a test run. I found this needs some more tweakings. But I failed to re-upload this file. The dropbox would not show the green 'create link' button; it displays just the blue 'download' button to my puzzlement. I have restarted my computer just to maybe shake off something like heap memory to no avail.


The problem I caught is on the CA side when it is 1 UP, V13 shows 0 UP and X10 shows AS and V23 shows incorrectly as '1 and 0'.


Waiting for your reply on the uploading issue if you are awake at this moment.


Mark
 
Hi Mark ,


It is possible you have explained all of this to Faseeh in your earlier posts ; I would request you to confirm the following :


Every match can have one of three outcomes :


a. The match is tied , with neither side winning after all 18 holes are played

b. The match is won on the 18th hole

c. The match is won on any earlier hole.


What should the result be displayed as , for each of the above 3 outcomes ?


Narayan
 
Narayan,


Yes and allow me to expand in the same order:


a. (Match is tied) If it is zero, the cell should display "AS"

b. (Match is won on the 18th Hole) If it is 1, the cell should display "1 UP". If it is 2, the cell should display "2 UP".

c. (Match is won on any earlier hole) Up till and including Hole 17 is greater than or equal to 2, the cell should display "x and x".


Any suggestion on the Dropbox issue?


Mark
 
Hi Mark ,


a. If the match is tied , the holes won score will be the same for both teams ; I don't understand your words "if it is zero" ; if the scores after 18 holes are the same , then the match is tied , right ?


b. If the match is to be won on the 18th hole , it follows that after 17 holes , the match is tied , with both teams having won the same number of holes. So which ever team wins the 18th hole will win the match ; a match cannot be won on the 18th hole if either team were 2 UP ; yes , if after 17 holes , one team were 1 UP , then after the 18th hole , if the same team wins the 18th hole , it can win the match 2 UP. If that team loses the 18th hole , then the match will be tied. Right ?


c. If a match is to be won on any earlier hole , that can happen only if the plus score in favour of one team is more than the number of holes remaining to be played e.g. if after 13 holes , one team has a plus score of 6 , then since the number of holes yet to be played is only 5 , the team which has a score of +6 will win 6 and 5. Right ?


d. It also follows that a match can never be won in the first 9 holes ; it can be won at any point after 10 holes have been played.


As far as the DropBox issue is concerned , I think there is some change which has been made by the website. When you see the blue colored download button , just click on the address in the address bar , and copy that address ; that is the share link.


Narayan
 
HI Narayan,


a. TRUE about the match being tied, it is called 'As Squared' or 'AS' for short. I am sorry about the word, zero - simply thinking it was evened out as zero. But that is actually a tie with the same number of wins or in a truly rare instance with tie for each hole through the entire game.


b. TRUE.


c. TRUE.


d. You got it correctly. '10 and 8' would be mathematically the maximum possible to win a match. I have never seen that happen.


Very good about your understanding of the match format. What is next? I suppose the next hurdle now is designing the layout and packing in the formulas so it would look like or close to my original layout design.


Did you find the info "1 UP" or "-" useful in the C8:T8 and C11:T11 useful for calculation. Do we need to display that Row 25 (COLUMNS 17 to 0)? I would like to have Excel flag the winning cell on the right side of the match between two teams/players. Best to do that with Conditional Formatting or in formula? That is for a start.


Thank you for the tip on Dropbox. Will get to that when it is time for uploading.


Good night,

Mark
 
Hi Mark ,


I have intentionally separated the method into several sections , so that it is easy to verify that the method is working correctly.


As and when we are sure that all cases have been verified and found correct , we can combine everything into one formula , though I wouldn't recommend that , since these extra rows can easily be hidden away in some corner of the worksheet.


Secondly , as far as I can understand , you want the results displayed in several formats :


1. AS


2. 1 UP


3. 2 UP


4. x and x


It would be nice if you could upload some earlier workbook of yours where all these results are present , so that testing will be complete.


Narayan
 
Hi Narayan,


Thank you for explaining your approach to this. I am going to upload my workbook for your peruse.


Very well, you have listed the results correctly.


You are right there is a change in how the Dropbox handles the 'Share Link'. What I see after clicking on the chain icon, the next screen would show just the blue 'Download' button. i did the right mouse click to Copy Link and paste here for the uploaded workbook file:


https://dl.dropbox.com/s/syfqmvtgylz6yrk/MatchFormat.xlsx?token_hash=AAEKyBy3Pc8-J_DsDHxnNZLW0fhn0tndF9zMrrLv4fDz8w&dl=1


Let me know if the download is working fine on your end. I hope so!


Mark
 
Hi Mark ,


Just downloaded your file without any problem.


Give me some time to verify the formulae in all situations , and then I'll upload the revised version.


Narayan
 
Hi Narayan,


How are you doing with my workbook thus far? Any question if I can be of any assistance to get over a hump.


Regards,

Mark
 
Hi Mark ,


Sorry , but I have not spent much time on your problem ; got diverted to too many others.


I'll get back to you on Monday ; the weekend is a good time to spend , if not with golf , at least with golf scoring !


Narayan
 
Hi Mark ,


Can you check your file here ?


https://www.dropbox.com/s/sbr71m4yoxuzspi/MatchFormat_1.xlsx


Please note that the formulae I posted earlier , will work only for the sheet tab labelled ONE.


Narayan
 
Hi Narayan,


Looks good. Now the next step is creating formula where to flag the winnig side with a 'win' text string. Or yet better to flag with a color pattern.


Mark
 
Hi Narayan,


Looks good. It is my focus on formulating the result in Column X, showing up on the winning side. If 'AS', it would then on both sides there.


Then what would be the best way to highlight the winning hole for the winning side between Columns C and U? We already use up the three CF allowed for that cell range. I suppose this idea is a matter of design. Is it possible to tell Excel to turn off the '1UP's and 'AS's beyond the point of the winning hole? It seems we can go either way if not both as a visual cue where the game is won at that hole. What is your suggestion? Hmm.


Mark
 
Hi Mark ,


Please note that the formulae I have used , do not make any use of the 1UP and AS cells ; if you wish , you can do away with them.


The file I have uploaded now puts the result in the appropriate cells.


https://www.dropbox.com/s/sbr71m4yoxuzspi/MatchFormat_1.xlsx


Narayan
 
Hi Narayan,


This should suit my purpose fine since it appears you would find it too much trouble to write a nested formula to tell Excel to cut off the 1UP and AS cells once the game is over. Then the mission is accomplished for the 'ONE' tab. I can see this Columns AB to AT range can be applied the same for the 'THREE' tab where a single golfer pits against the opposing team's single player.


Then we can move on to 'TWO' tab. The difference here with the FourBall Match play is to pick the lower strokes of each team for each hole. Two golfers on a team are playing their own balls. The formula would check for the lower stroke of each team pair then compare to see who win the hole otherwise as squared. You may find this easy to tackle.


Mark
 
Narayan,


Is that too much trouble to add a formula for the blank cells to turn off the 'AS' in the Columns C to U until strokes are entered?


Mark
 
Hi Mark ,


I have added CF for the cells in columns M through U ( holes 10 through 18 ) so that the winning hole is highlighted , but only for some rows ; I found it too much work to copy the formats to all the other rows. Can you check ?


https://www.dropbox.com/s/sbr71m4yoxuzspi/MatchFormat_1.xlsx


Regarding your other comments , I'll look into them.


Narayan
 
Hi Narayan,


You are right we oughta focus on Hole 10-18 for the winning hole. No chance for the first 9 holes. :) However I am unable to see the CF you did for Columns M through U to highlight the winning hole. However I see the third condition missing for the strokes part that I created beforehand. I suspect you would want to CF the rows where 1UP and AS cells are located, right? I don't see anything highlighted there to show the winning hole.


Mark
 
With the number of responses to two posts about Excel and Golf, now exceeding 150, I think there is a market for somebody to produce a Good Excel Based Golf Scoring/Handicapping system !
 
You must log in or register to reply here.
Back
Top