msmith4012
asked on
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
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
ASKER
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
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
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
ASKER
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.
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(SUM
Sid
=IF(SUM(E3:H3)>0,IF(OR(SUM
Sid
Sorry I meant
=IF(SUM(E3:H3)>0,IF(OR(SUM
in i3
Sid
=IF(SUM(E3:H3)>0,IF(OR(SUM
in i3
Sid
ASKER
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
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
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
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
ASKER
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
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
I had one more question though. If you can see my last post.
Sid
ASKER
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.
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
You may change the Ratings in Row 2.
Sid
Pin-High-Group-Golf-Skins-Calcul.xls
ASKER
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
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
Just a small change in the formula.
Please check it now?
Sid
Pin-High-Group-Golf-Skins-Calcul.xls
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Sometimes we experience things in life that really exceed our expectations. EE is one those experiences.
Glad to be of help :)
Sid
Sid
ASKER
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
ASKER
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
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
=COUNTIF(J3:J20,"X")
Sid
ASKER
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.
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.
ASKER
Sid,
I don't see any changes. Still shows Dave as the winner. There should be no winner.
Hole #7.
Mike
I don't see any changes. Still shows Dave as the winner. There should be no winner.
Hole #7.
Mike
ASKER
Perfect. Thanks Sid.
Mike
You are welcome :)
Sid
Sid
ASKER
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
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
If you get stuck simply open a new question (Related Question) or paste the link of that question her :)
Sid
what needs to be done?
Sid