We help IT Professionals succeed at work.

Golf Skins Calculator by Patrickab

msmith4012
msmith4012 asked
on
3,809 Views
Last Modified: 2012-05-11
I'm sending this message in hopes that Patrickab responds.  

Patrick:  Back in 2008 you designed an initial golf "skins" calculator that is very close to what I'm looking for for my golf group.  I need your help in completing it with a few tweaks.  I'm just not skilled enough (yet) to fully understand the nested IF functions, but I'm sure you can do this in your sleep.   Would appreciate it any and all could help get this message to Patrick.  

Please see Sheet1 of the attachment that Patrickab designed at that time.  

Thanks!

Mike  Pin-High-Group-Golf-Skins-Calcul.xls
Comment
Watch Question

Patrick is on a holiday :)

what needs to be done?

Sid

Author

Commented:
Sid,

Thanks for such a quick reply.  Not sure if you play golf or not, but basically a "skin" is simply a bet for the winner of a hole - lowest score wins.  The first adjustment I need is that each golfer has to shoot "par" or better(less)  in order to win a skin.  In other words, if we are playing a Par 4 Hole, and the scores are: Tom/5, Dick/6, Harry/7, Sam/6, then no one wins the hole because none of the players scored a "par" or better - even though player A had the lowest score.  So, I added a column to show the Par Value for each hole in Sheet 3.  .  

The second is a bit more complicated. In our group we designate "A" players and "B" players. "A" players are better golfers than "B" players.  So, to make the betting more equitable, if an A player and B player tie for the win, the B player actually wins the tie breaker.  For example, if there are 4 players in a group:

Tom - 3 (A player)
Dick - 4
Harry - 5
Sam - 3 (B player)

In the above example, Sam wins the tie-breaker because he is a B player.  The other wrinkle to this is that we only allow the tie-breaker rule for the 9 most difficult holes on the course.  The 9 hardest holes are designated by "Handicap" column.  If a hole has a "handicap" of 1-9, then it falls into this category.  The tie-breaker rule does not apply to Handicap Holes 10-18, or said another way, the 9 easiest holes on the golf course.  

So, I'm trying to figure how to write a function that only awards a "skin" or "winner" to those that score lowest score on each hole with the requirement that one make "par" or better, and how do I designate a "B" player and the tie-breaker for the nine most difficult holes (designated by "handicap 1-9).  

Make sense?  is this achievable?  Thanks much

Mike
No I don't play Golf but will still try to help you as much as I can. :)

Quick Question.

>>>In other words, if we are playing a Par 4 Hole, and the scores are: Tom/5, Dick/6, Harry/7, Sam/6, then no one wins the hole because none of the players scored a "par" or better - even though player A had the lowest score.  So, I added a column to show the Par Value for each hole in Sheet 3.  .  

I checked Sheet 3. The Par value for Hole 4 is 5 in that sheet so doesn't that make Tom a Winner?

Sid

Author

Commented:
Sid,

Sorry, for my example above I wasn't referencing the spreadsheet, just giving an example for a hole with a par value of 4.  .  

Look at Hole #2 in Sheet 3.  This is a Par 4 Hole.  Harry had the lowest score with a 5, but he didn't make a par of 4 or better, so there shouldn't be winner.  That's what I'm trying to fix.  
You mean this in Cell I3?

=IF(SUM(E3:H3)>0,IF(OR(SUMIF(E3:H3,MIN(E3:H3),E3:H3)<>MIN(E3:H3),SUMIF(E3:H3,MIN(E3:H3),E3:H3)>C3),"No winner","Won"),"")

Sid
Sorry I meant

=IF(SUM(E3:H3)>0,IF(OR(SUMIF(E3:H3,MIN(E3:H3),E3:H3)<>MIN(E3:H3),MIN(E3:H3)>C3),"No winner","Won"),"")

in i3

Sid

Author

Commented:
Sid,

That's great!  This definitely solves the first problem.  

Now, how do I solve the "A" player versus "B" player tie-breaker?  So, for the sake of argument, let's assume that Harry is our "B" player and the rest are "A" players.  How would I solve a tie-breaker between Harry and Dave?  Also, how do I only allow the tie-breaker rule for the holes designated as "handicap 1-9"?  

Mike
Gr8 :)

I do this and you teach me how to play Golf :)

Ok Jokes apart. Where have you mentioned their Ratings? I mean "A" or "B"?

Sid
Also please consider this situation

Handicap      2
Par: 4
Dave: B SCORE: 4
Tom: B SCORE: 4
Dick: B SCORE: 6
Harry: A SCORE: 4

Who is the winner?

Sid

Author

Commented:
Sid,

I won't teach you how to play, but when you do play, you'll know how to bet! :0)

I haven't mentioned their each golfer's rating per se, but just wanted to know how you would do it.  So, in the spreadsheet let's assume Harry is a B player, and Dave, Tom, and Dick A players.  

I would guess that a separate column would need to made for Harry's initial scores, then another that calculates Harry's "adjusted" score if there is a tie-breaker with an A player.  Again, this is only for the 9 hardest holes.  

Mike
Fair enough :)

I had one more question though. If you can see my last post.

Sid

Author

Commented:
Also please consider this situation

Handicap      2
Par: 4
Dave: B SCORE: 4
Tom: B SCORE: 4
Dick: B SCORE: 6
Harry: A SCORE: 4

Who is the winner?

There's no winner in that scenario, because the two B players have the same score.  The lowest score has to win.  In this case. Dave and Tom essentially receive an adjusted score of 3 versus Dave's unadjusted score.  So, Dave and Tom are tied = no winner.
Not sure (As my forte is not formulas) if this is what you want?

You may change the Ratings in Row 2.

Sid
Pin-High-Group-Golf-Skins-Calcul.xls

Author

Commented:
Sid,

You are so, so close.   It works in every iteration except this one:  

Input the following for Hole #2:  

Dave B - 4
Tom B- 5
Dick A - 5
Harry A - 4

Dave should win the hole b/c he wins the tie-breaker with Harry, but in the spreadsheet "No Winner" is declared.  

The funny thing is that it works for Hole #1, but not the other holes.  So, if you input the same scores for Hole #1, and Hole #2 as above, Dave should win.  






























Mike
Oh I got it.

Just a small change in the formula.

Please check it now?

Sid
Pin-High-Group-Golf-Skins-Calcul.xls

Author

Commented:
Checked Hole #1 again with following scores:

Dave B - 5
Tom B - 4
Dick A - 4
Harry A - 5  

Tom should win the tie-breaker with Dick, but "no winner" in spreadsheet.  So close though!
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Unbelievable experience.  Sid was awesome, and had a great attitude regarding my somewhat convulted explanations of my objectives.  Highly recommended!

Sometimes we experience things in life that really exceed our expectations.  EE is one those experiences.
Glad to be of help :)

Sid

Author

Commented:

Thanks Sid.  I must say I'm a tad bit jealous of your skills.  Wish I better understodd those nested logical functions and arrays. I woulld not have thought to simply create an A & B player array.  

Again, appreciate your help.  

Mike

Author

Commented:
Hey Sid,

One more thing.  How do I count the all the cells with "X" in them so we can pay out the bets?  In other words, we want to count all the winners then divide into total amount of money we put in the pot.  

Sorry, I forgot about this.  

Mike
Like this in J21?

=COUNTIF(J3:J20,"X")

Sid

Author

Commented:
Check out hole #7.  Similar problem as before:  

Dave B - 3
Dick B - 3
Tom A - 3
Harry A - 5

No winner here as two B players are tied.  Spreasheet shows Dave as the winner.  

Author

Commented:
Sid,

I don't see any changes.  Still shows Dave as the winner.  There should be no winner.  

Hole #7.  

Mike
Oops uploaded the wrong file.

here is the right one.

Sid
Pin-High-Group-Golf-Skins--Lates.xls

Author

Commented:

Perfect.  Thanks Sid.  

Mike
You are welcome :)

Sid

Author

Commented:
Sid,

How difficult would it be to add golfers to this?  So, if I had 20 players, would I simply add more of the "if" loops that I see in your formula that relates to the cell of the add'l golfers?  

Mike
It Shouldn't be difficult and yes, insert columns and set the Ifs()

If you get stuck simply open a new question (Related Question) or paste the link of that question her :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.