• 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 downloaded the file from the link I have posted , and I am able to see a green highlight of cells U31 , S52 and Q61 on the tab labelled ONE.


Narayan
 
Hi Hui ,


Your suggestion is good , but I think we have just scraped the tip of an iceberg ; to convert whatever we have done so far into a proper application , I think a lot of additional effort will be required ; and without knowing whether there is a market for such an application , who will venture forth ? Chandoo , maybe ?


Narayan
 
Hi Narayan,


I am unable to see the green highlights on cells U31, S52, and Q61 on my end. Me now puzzled.


On the marketing comment, I was unable to find a golf software that has the specific format I have designed. This is what brought me to this site. I concur with Narayan that we are just scraped the tip of an iceberg. Much of the correspondence spent on explaining how the golf format should work. There is more to be desired, but I need to be reasonable with the kind assistance he has freely given me thus far.


My next and last hope from Narayan (or anyone else) is for the help with the dashboard (DASHBD) tab to tally the team scoring and individual golfer stats.


I would be happy to advise anyone who wants to venture into the building of an application from where we are now.


Mark
 
Hi Mark ,


Is it that your Excel for Mac version does not allow more than 3 rules in Conditional Formatting ?


If you open the CF dialog for any of these 3 cells , are you able to see 4 rules as follows :


1. =AND($AW31>0,$AW31=COLUMN(M31)-2)


2. =M31=M$14-2


3. =M31=M$14-1


4. =M31=M$14


in the above order ; if not , please introduce them , and see if you get the highlights.


Narayan
 
Hi Narayan,


Exactly the culprit. My Excel 2008 for the Mac only allow 3 rules and as a result the #4 got bumped out, leaving #2 and #3 in exhibit and #1 not present. Hence I would not be able to introduce the #1, unless I give up the #2. Hmm. I would rather utilize the rows with 1UP and AS cells and only 1 rule is employed there.


In my CF dialog I have as follows:


1. Cell Value Is Equal to ='1UP' (returns as a Light Grey)


2. Formula is =AND($AW31.0,$AW31=COLUMN(N31)-2 (returns as a Lemon Yellow)


If I add Rule #2 to the CF dialog above, would those two rules cancel each other out as a result? Should you have an alternate idea?


If I don't hear from you shortly, I call it a night. See you early in the morning.


Mark
 
Good day Faseeh and NARAYANK991


I think you should both get together and copy right the program/code you have made and then sell it to the golfing fraternity, after all the work you have done you deserve some reward.
 
Hi bob,


Nrayyan is way to keen to pocket the ball, (if i have used the phrase correctly :) Atleast, he deserves a trophy. :D


I am sure that if a golfer pay this much attention to the game of Golf, he can won PGA. ;)


Faseeh
 
Hi Faseeh ,


In golf , the trophy is given to the golfer who pockets the ball in the least number of strokes ; going by the number of posts we have taken , we will need a handicap of 50 or so !


I have enjoyed the whole interchange of ideas on this fascinating and , at least to me , unknown aspect of golf.


Narayan
 
@ Narayan,


LOL about handicap of 50+. The cut-off is mercifully at 36 in the US Golf Association handicap books. In other words, USGA reminds this golfer to take lessons to lower it.


Never heard of Ryder Cup (Team US vs Team Europe) or even President's Cup (Team US vs Team Asia) in addition to World Match Championships (the world's top 64 pro golfers) right here in Arizona? They all played in a match format.


I am now up if you have something to post.


Mark
 
Hi Mark ,


Sorry , but it's time for us , in this part of the world , to go to bed !


We can use a macro for purposes of highlighting the winning hole , if that is OK with you.


Narayan
 
Hi Narayan,


Let's hold off the macro for highlighting the winning hole. I might upgrade to Excel 2011 for Mac and that should allow me more than the limit of 3 rules in Excel 2008 for Mac.


I have uploaded the improved file for you to check into: https://www.dropbox.com/s/xsvqgie83g4whzd/MatchFormat_2.xlsx


In it, what I have accomplished is restoring the CF to show the 3 color pattern rules in the range for strokes. Next I did using the same formulas from the ONE tab to start the THIRD tab on Rows 17 through Rows 39. Verify that they are correctly done, especially the formula in cell AV32, where it should return a number value rather than 'FALSE'. It seems with each AZ win, it returns 'FALSE'. Then I can finish for the rest of the range in THIRD tab.


Then I need the formula to tally the team points in V8 and V9 in ONE tab. One point worth for a win and half point each for a tie (AS). Repeat for the same locations in TWO and THREE tabs.


I am also awaiting for your formula help with the TWO tab where I explained about comparing the lower score of a team pair in a previous posit.


Mark
 
Hi Narayan,


Just a couple more quickies needed for the ONE tab;


1. formula to tally all the match games one point for a team win and half point for a tie in cells V8 and V9.


2. for the 1UP and AS rows, a nested formula to leave blank until strokes are filled. Use ISBLANK?
 
Hi Mark ,


Can you check the file here ? Point 1 has been taken care of.


https://www.dropbox.com/s/8yb6n1e7ims9m62/MatchFormat_2.xlsx


For point 2 , I think the easiest is to check if the relevant value in column W is greater than 0 ; if so , populate , else leave blank.


Narayan
 
Hi Narayan,


The formula you wrote for the team wins tally is cleverly efficient. I am tickled here.


For the ISBLANK, where do I insert the ISBLANK() to read cell W17 in this nested formula;

=IF(C19>C17,"1UP",IF(C19=C17,"AS"," "))?


Then go to THREE tab to see why the formula returns false for the winning AZ team/player. Check AV34 and AV39 and tell me.


Mark
 
Hi Narayan,


I tried to type the whole formula you wrote in cells V8 and V9 of ONE tab into the same cells in THREE tab and made sure the range is entered longer as from Row 17 to Row 133 for the THREE tab. The cell V8 in THREE tab however returned with a "#VALUE!" It is the same problem encountered with inserting the formula in Column AV from ONE tab to THREE tab. It seems to have to do with array?


Mark
 
@ bobhc, thanks for the array command tip. That explains away any pertinent error message I wondered about.


@ Narayan, disregard the ISBLANK question. I got it now.


Next is how I caught an oversight by looking at cells X47 and X73 in THREE tab where it returned with a string of '2 and 0'. That is a no-no. The minimum for 'x and x' is '2 and 1'.


Thus I am checking how we have so far for result purpose those text strings that are currently up; 'x and x', '1UP' and 'AS'. This means the "2UP" was left out. So please fix that in the responsible formula. Tell me where you edit that and I could help copying to the rest of range in ONE and THREE tabs.


Here is the reuploaded MatchFormat_3 file where you can see the problem: https://www.dropbox.com/s/r00nhclklxggfbk/MatchFormat_3.xlsx


After fixing this '2UP' issue, we are then ready to attack with formula for Columns AA:AW in TWO tab. This match format called a FOURBALL is about flagging the lower strokes from a pair of teammate golfers and compare against the lower strokes out of the other pair on the opposing team.


Mark
 
Hello Narayan?


Did you see my reporting about the missing '2UP' in what I presume the responsible formula found in Column AT. Can we fix the first one there that is in cell AT18. I noticed this is an array formula, meaning I need to use the CTRL +SHIFT+ENTER command in copying the edited formula for rest of the range in ONE and then THREE.


I am anxious for what you can come up with a formula in TWO tab if you read the posit above this one.


Mark
 
Hi Mark ,


Yes , I read your earlier post , and have been thinking it over. I think the tab labelled TWO is going to be a tough one , easier tackled using VBA.


Narayan
 
Narayan,


OK, can we at least begin working on the missing '2UP' in cell AT18 so we can have that squared away while you mull over VBA option. What is VBA as opposed to a macro, may I ask. Enlighten me what would involve with VBA if that has to do with what version I have in Excel?


Mark
 
Hi Mark ,


VBA procedures and macros are essentially the same ; you have indicated earlier that the version of Excel which you have supports VBA programming. If it does , then I don't think you will need to do anything except run the procedures when required.


Narayan
 
Hi Mark ,


Can you check this version ? The point about a 2 UP result has been sorted out.


https://www.dropbox.com/s/c4xy4ohzos2rn6a/MatchFormat_3.xlsx


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