Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2523
  • Last Modified:

Golf Skins Calculator by Patrickab

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
0
msmith4012
Asked:
msmith4012
  • 18
  • 14
1 Solution
 
SiddharthRoutCommented:
Patrick is on a holiday :)

what needs to be done?

Sid
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
msmith4012Author 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.  
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
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
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
Fair enough :)

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

Sid
0
 
msmith4012Author 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.
0
 
SiddharthRoutCommented:
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
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
Oh I got it.

Just a small change in the formula.

Please check it now?

Sid
Pin-High-Group-Golf-Skins-Calcul.xls
0
 
msmith4012Author 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!
0
 
SiddharthRoutCommented:
In my spreadsheet, it is showing that Tom is the winner.

Sid
0
 
msmith4012Author 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.
0
 
SiddharthRoutCommented:
Glad to be of help :)

Sid
0
 
msmith4012Author 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
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
Like this in J21?

=COUNTIF(J3:J20,"X")

Sid
0
 
SiddharthRoutCommented:
0
 
msmith4012Author 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.  
0
 
SiddharthRoutCommented:
Please check this.

Sid
Pin-High-Group-Golf-Skins-Calcul.xls
0
 
msmith4012Author Commented:
Sid,

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

Hole #7.  

Mike
0
 
SiddharthRoutCommented:
Oops uploaded the wrong file.

here is the right one.

Sid
Pin-High-Group-Golf-Skins--Lates.xls
0
 
msmith4012Author Commented:

Perfect.  Thanks Sid.  

Mike
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
 
msmith4012Author 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
0
 
SiddharthRoutCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now