• 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

D

deaflarch

New Member
In a golf game, playing the match format is determined by winning on a hole-by-hole basis. Unlike stroke play, in which the unit of scoring is the total number of strokes taken over one round of golf, match play scoring consists of individual holes won, halved or lost. On each hole, the most that can be gained is one point. Golfers play as normal, counting the strokes taken on a given hole. The golfer with the lowest score on a given hole receives one point. If the golfers tie, then the hole is halved.


For example, in an 18-hole match, the first hole is a par-4 and Player A scores a 3 (birdie) and Player B scores a 4 (par); Player A is now 1-up with 17 to play. In the same match on the second hole, a par-5, Player A takes 8 strokes and Player B takes 5 (par); Player B wins the hole and the match is now "all square" with 16 to play. On the third hole, a par-3, both players take 3 strokes and the match is all square with 15 holes to play.


Once a player is "up" more holes than there are holes remaining to play the match is over. For example, if after 12 holes Player A is 7-up with six left to play, Player A is said to have won the match "7 and 6". If both finish the game "as squared" or tied, each will get a team half point.


Let's start looking at the X column inside the 'ONE' tab. What I have done in the past is tediously adding up the scoring manually and type the result on the correct side of the winner in the X column. Is it possible to write a formula to do this calculation automatically in the X cell and eliminate human error? We can begin with the Rows 23 to 27 and right now the 'AS' is entered manually in X24:25 and X26:27 as an example.


https://www.dropbox.com/s/syfqmvtgylz6yrk/MatchFormat.xlsx
 
Hi deaflarch,


Talk in terms of the Group 2, I have understood the process of how you give 1UP and AS, but how did you concluded the result in X24:25 and X26:27. Explain in terms of this table.


Regards,

Faseeh
 
Hi Faseeh,


What you see in the Group 2 with the nested IF formulas in C23:U23 (all 18 holes) for Team California and the same in C:28:U28 for Team Arizona; they are all simply return as the 1UP or AS for each adjacent cell and not the final 'win match' answer.


Here the central question is how to write a formula (or a series of formulas) in the U column or somewhere else to return the official result in the U24:25 if Team California wins this match or U26:27 if Team Arizona does. Keep in mind should this formula do a logic test and determine where the match is over before if not at the finish where it would end in three possible ways; a tie (AS) or 1 UP or 2 UP. More on this in the next paragraph.


In the 'x and x' case when the match is won before the last hole, the most a team/player mathmatically could win the match is '10 & 8' at end of tenth hole and the least would be '2 and 1' at end of 17th hole. The only possible scenario for either '2 UP' or '1 UP' official result would occur when a team/player is 1 UP entering the 18th and ice the match with either 'AS' or '1UP' on the final hole. The final AS result is a mathematical tie.


To use a simple COUNTIF formula, let's say in W23 and W28 for all 18 holes, to add up a sum total of 1UPs is not the final answer, but could be part of the data assemblage towards the official result.


If you have any further question, fire away.


Thank you!

Mark
 
Let me add here:


After the origiinal central question gets solved, we would test with each group in the workbook. Then we may use possibly a COUNTIF fomula in both V8 and V9. Each team win is worth one point and each team tie is worth a half point.
 
Hi deaflarch,


I have never played golf in my life (and i am 29 years old :) so digesting these rules will take some time. My question stands as it was, please do not use the technical terms.


Please explain step by at what point in this table you are making the decision?? are we comparing L24 & 24 with 36??
 
Hi,


Few confirmations/questions:


1. Palyers plays against a given number of strokes, which results in 1UP, 2UP or AS or tie. In respective cases we mark 1, 2, 0.5 & 0.5?? Is that correct?


2. We will be summing up scores, the cumulative score for every player will be compared on stroke-to-stroke basis with the remaining number of strokes? Is that correct? You can fined these summed up scores in row 7 & 15 of my sheet? Is that correct?


3. What if at no point the cum_score is greater then the strokes remained?? How will then game end?


4. With reference to the sheet i have uploaded, can you tell the stroke where the mentioned criteria (cum_score > strokes remaining) is being met?


5. What is the use of OUT & IN??


http://dl.dropbox.com/u/60644346/Golf_Sample.xlsx


..Struggling with Golf Scoring Sys. :)


Faseeh
 
First of all, I realized the L23 and L28 should not have the IF formula. I stripped that and the rest of L cells in the workbook. Here is the update:


https://www.dropbox.com/s/hjxivts4t6medj1/MatchFormat%20.xlsx


I understand your appeal against the technical terms. I try to present it the way an "Excel mind" would read and interpret the logic test challenge. OK, here we go for Group 2 match game:


I will be using the team tags as an example; CA and AZ as seen in A24:27. Keep in mind, my third goal is tallying result and assigning to the individual in the referenced cells (B13:F38 and H13:L38) under the win/loss/tie columns in DASHBD (Dashboard) tab after we figure out this original question.


After Hole 1, CA gets 1 UP & 17 more holes to go.

After Hole 2, it is back to AS & 16 to go.

After Hole 3, it stays AS & 15 to go.

After Hole 4, CA is 1 UP again & 14 to go.

After Hole 5, CA lead increases to 2 UP & 13 to go.

After Hole 6, CA goes 3 UP & 12 to go.

After Hole 7, CA lead drops to 2 UP & 11 to go.

After Hole 8, CA lead remains at 2 UP & 10 to go.

After Hole 9, CA goes 3 UP & 9 to go.

(Disregard the data in the L column - that is for subtotal score for the front nine)

After Hole 10, CA is now 2 UP & 8 to go.

After Hole 11, CA 2 UP carries & 7 to go.

After Hole 12, CA goes 3 UP & 6 to go.

After Hole 13, CA 3 UP carries & 5 to go.

After Hole 14, CA downs to 2 UP & 4 to go.

After Hole 15, CA back to 3 UP & 3 to go. (CA is on verge of winning here but the match carries on still)

After Hole 16, CA lead shrinks to 2 UP

After Hole 17, CA is down to 1 UP

After Hole 18, the match is then TIED! Since this is the last hole, thus half team point is awarded to CA and AZ.


Compare this to Group 7, after the 14th Hole, AZ is 5 UP with 4 holes to go. In the worse case scenario where CA may win the next four holes, but the fate is already sealed in favor of AZ with at least a 1 UP finish. Excel shall recognize that and stop the execution with a "TRUE" value and return the official result on the X61:62 merged cell. Can we do that with the "5 & 4" text string value. Does this means we need to list all the possible text values? Or let Excel count the remaining holes and plug in on the RIGHT SIDE x of 'x & x' text string value?


Your turn here.
 
In answering your confirmation/question post:


1. Yes the number of strokes determine how the hole is won or tied. But I would not give a 0.5 value for the AS. Only a 1 value for the win on which side of the team pair.


2. You correctly answer in B17 on how the game ends or may remain tied after the last hole.


3. Same answer as #2


4. Minus the 0.5 value, you get the drift here. The point is to "announce" where the game ends with a text string value. As a visual clue bonus, if we could do Conditional Formatting to flag with pattern color the cell (purple is my choice) at the side of the hole where the game is decided.


5. OUT (L24:27) is the subtotal score of front Nine and IN (V24:27) is the subtotal score of back Nine that gives the total Score (W24:27) for individual strokes. We are playing for Match results to determine the team winner for the tournament called Desert Cup.
 
If you have access to the Golf Channel on your TV, there is a World Match Play going on right now here in Arizona that will give you a clear idea how a match format is played.
 
Hi deflarch,


when u were watching match my clock was showing 2 am! :)


Let me understand what u just posted.


Faseeh
 
Hi deaflarch,


Can you confirm the results of this worksheet, see comments as well:


http://dl.dropbox.com/u/60644346/Golf_Sample_Revised.xlsx


Regards,
 
Hi deaflarch,


This one is further improved can you please confirm?


http://dl.dropbox.com/u/60644346/Golf_Sample_Single%20Step%20Solution.xlsx


Faseeh
 
Faseeh,


You got it right! I noticed that you are eliminating the OUT and IN cells in your Sample workbooks. Was that helpful to simplify the formula writing in defining ranges? And the same holds true for dropping the 'AS' text value in the holes range? I just want to understand your thinking here.


One correction here: the '5 over 4' is not about 4 strokes left to go. It is about how many holes left to go. So we just simply edit replacing 'over' with 'and'.


Now shall we need to somehow expand the nested formulas for the 'x and x' text string value to handle as I mentioned earlier about the unique situation coming to the last hole when a team/player leads by 1 UP. I will create a couple of possible scenarios to use this text string value, "x UP':


After 17th hole, Team A leads by 1 UP then AS on 18th = final result reads as '1 UP'


After 17th hole, Team A leads by 1 UP then earned another 1 UP on 18th = final result reads as '2 UP'


The Group 3 in ONE tab is a good exercise for the question above. I will pause here to see your comment on the above.


Mark
 
Faseeh, allow me to add a third scenario:


After 17th hole, Team A is 'AS' with Team B, then win the last hole = the final result reads as '1 UP'. (the reverse of the first scenario above)


Disregard my question on your dropping the 'AS' text value. I realized it is still there after another look. It is now half past 11 at night. Will check on you in the morning.


Regards,


Mark
 
Hi deaflarch,


Good Morning, Can you try these formula:


On any sheet:


In Cell V13:


=IF(COUNTIF($C$13:$T$14,"Won")=0,"AS",IF(COUNTIF(C13:T13,"Won")=1,(COUNTIF(C8:S8,"1UP")-COUNTIF(C11:S11,"1UP"))&" UP",""))


In Cell V14:


=IF(COUNTIF($C$13:$T$14,"Won")=0,"AS",IF(COUNTIF(C14:T14,"Won")=1,(-COUNTIF(C8:S8,"1UP")+COUNTIF(C11:S11,"1UP"))&" UP",""))


and V15:


=IF(COUNTIF($C$13:$T$14,"Won")=0,"AS",IF(COUNTIF($C14:$T14,"Won")=1,INDEX($C10:$T10,0,18-MATCH("Won",$C14:$T14,0))&" Over "&(18-MATCH("Won",$C14:$T14,0)),""))


...I assume that there are no more criteria awaiting :D


Faseeh
 
Faseeh, you are correct there are no more awaiting for this kind of result. I did not want to confuse you with this small technical twist at the end of the game until you are clear about the concept of how the match format is played.


But now I am befuddled with the result of your last instruction for those 3 cells, V13, V14, and V15. Here is the uploaded file with what I did for you:


https://www.dropbox.com/s/jdai0gvxa58n9fr/Golf_Sample_R2.xlsx


Feel free to correct and re-upload the file.


Mark
 
Hi deaflarch,


Please check and comment on this file, please check it for all seven games on sheet named One in your original file:


http://dl.dropbox.com/u/60644346/Copy%20of%20Golf_Sample_Single%20Step%20Solution.xlsx


Regards,
 
Faseeh,


Yes, it is confirmed what you are doing correctly for the GAME tab. Can we show the '5 and 4' rather than 4 UP?


Since I was unable to apply your formula per your request for confirmation to the seven groups in the ONE tab due to merged cells. So I instead took down Group 3 results on pad and plug those numbers to C9:T10 and the final result is correct.


Now how do you propose we do next? Should I redesign the layout or you can show me how to pack the formulas in place without the redesign?


Notice how I use CF to flag the cell where the 'Won' returned as TRUE. Is it possible to copy and paste this CF globally at once? Here is the re-uploaded file:


https://www.dropbox.com/s/4x3v9jbpv2sqt2e/Copy%20of%20Golf_Sample_Single%20Step%20Solution.xlsx


Mark
 
Hi deflarch,


Good Morning, Let me look at this. and i will revert shortly.


Edit: My file is already showing V15. is it correct?


Faseeh
 
Faseeh, Good morning for me now. :D


I checked the cell V15 in the GAME tab once again and it reads correctly but should be 'and' nor over as in '5 and 4'. You need to show me how to make Excel show either AS, 1 UP, and 2 UP otherwise it would return as 'x and x' on the side of a winning team/player. That was the main objective of formula writing.


Mark
 
Hi deaflarch,


Good Morning, although its 9:00 pm here :)


Please try this formula instead:


=IF(COUNTIF($C$13:$T$14,"Won")=0,"AS",IF(COUNTIF($C14:$T14,"Won")=1,INDEX($C10:$T10,0,18-MATCH("Won",$C14:$T14,0))&" and "&(18-MATCH("Won",$C14:$T14,0)),""))


The formula is written to show you some thing only when he has got one. Can you show me a situation where it is giving X over X results.


..am waiting for reply. Till you reply am playing miniclip :)


Edit: Played two matches of 8 ball pool, lost both, now logging out :D


Regards,
 
Faseeh,


Ok, let me try again about scenario buildling for Excel formulas to do logic check and whether to return with a 'x and x' (not 'over') or not. The 17th Hole is the pivotal hole where and when the difference is narrowest, just those two, As Squared or 1 UP on either side. From there, the final result after finishing 18th Hole would conclude with one of those three string values; 'AS', '1 UP', or '2 UP'. Again there shall be no possible 'x and x' situation at the 18th Hole.


With 2 UP or greater from the 17th Hole and back, the 'x and x' shall always apply. You could see mathematically how it works for instance, with 2 UP after 16th Hole, two possible scenarios shall unfold; Either team/player squared on 17th and the game concludes as '2 and 1'. Otherwise a team/player got 1 UP and it concludes as '3 and 1'.


This is why at the end of 17th Hole with 2 UP or greater on either side is the pivotal time where the logic check could return whichever set of values as discussed above. Does this make sense now?


Mark
 
Hi deaflarch,


Sorry for this day-late reply. can you check the following file:


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


Hope to listen from you soon.


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