Golf game calculation

[Rock5150]

I calculate manually a golf game called "Rabbits and Squirrels" that rewards golfers for pars (rabbit) and squirrels (Birdie or better)

I want to generate a spreadsheet where each golfer is listed below and calculates any winnings they have, where they won money from a particular hole(s). Each person kicks in $20 to create the total money

A Rabbit is when there is one lone golfer that has the lowest score on a specific hole. That score though must be at least a par. For example in hole #5, Golfer A was the only person to par the hole ("4"). Therefore that person would get a "rabbit".

A Squirrel is when there is one lone golfer that has the 'only' lowest score below par. For example, in Hole #11, Golfer C had a "3" which is two strokes below par. Even though Golfer A and Golfer G got a "4", its not the lowest because Golfer C got a "3" (two strokes below the par 5).

When a person birdies or better a hole, that person can win a squirrel AND a rabbit. In the example besides #11, Hole #17 is an example where one person won a squirrel with a birder on 17 (the only one) and also got the lowest score (rabbit)

However, the important factor is if a rabbit or squirrel was 'held' by someone in a previous hole, that new person would let loose the rabbit and squirrel

Its possible to let loose a rabbit but keep the squirrel

What I tried to do is give a manually calculated example below to provide context.

Total Money

Each person kicks in $20

Each Rabbit is worth $33.33 for every six holes (shaded below)

Each Squirrel is worth $33.33 for every six holes

If the rabbit and/or squirrel is running, it goes to the "skin" pot Hole 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Par 4 4 5 3 4 4 5 3 4 4 5 4 4 3 4 4 3 4 Golfer A 4 4 6 3 4 4 6 3 4 4 4 4 4 2 4 4 4 4 Golfer B 5 5 5 3 7 4 5 3 5 5 5 5 5 3 5 4 5 5 Golfer C 5 5 6 3 5 4 5 3 5 5 3 5 5 3 5 4 2 5 Golfer D 4 3 5 4 7 4 5 3 4 4 5 4 4 3 4 4 4 3 Golfer E 4 5 4 3 5 4 5 3 4 4 5 4 4 4 4 4 2 4 Golfer F 4 5 5 4 5 3 7 3 4 4 5 4 4 3 4 4 2 4 Golfer G 5 6 5 3 5 5 7 5 3 4 4 5 5 3 5 3 5 5 Golfer H 4 5 5 3 5 4 4 6 4 4 5 4 4 3 4 4 4 4 Golfer I 4 5 5 3 5 4 5 4 4 4 5 4 4 3 4 4 4 4 Golfer J 4 5 5 4 5 5 4 4 4 4 5 4 4 3 4 4 4 4 In the above manually calculated for holes 1-6 Hole 1 Everything is running Hole 2 Golfer D has a rabbit and squirrel. Picks up a "skin" Hole 3 Golfer E Lets loose the rabbit and squirrel. Picks up a "skin" Hole 4 Everything is running Hole 5 Golfer A picks up a rabbit. Picks up a "skin" Hole 6 Golfer F lets loose the rabbit and wins the squirrel. Picks up a "skin". Wins $33.33 for winning the squirrel. No money collected for the rabbit in this six holes That $33.33 not collected on the rabbit the first six holes goes to the "skin" pot which is split up among those who pick up a skin for achieving the lowest score on the hole (Golfer D, E, A and F) Hole 7 Everything is running. Even though Golfer H and J birdied, since there were two, everything pushes Hole 8 Everything is running. Multiple golfers parred that hole Hole 9 Golfer G picks up the rabbit and squirrel. Picks up a skin Hole 10 Golfer G maintains the rabbit and squirrel. Golfer didn't bogey that hole and no other golfers had a birdie or lowest score Hole 11 Golfer C now knocks the rabbit and squirrel out since Golfer G had it. Its loose. Golfer C picks up a skin Hole 12 Golfer C loses the rabbit since he bogeyed the hole (shot above par) and there were others who parred hole. However keeps the squirrel. In this six, Golfer C keeps a Squirrel so gets $33.33. The other $33.33 (Rabbit) was not claimed so goes to skin pot now at $66.66 (the running rabbit from the first six holes plus the running rabbit in 'this' six holes) Hole 13 Everything is running Hole 14 Golfer A picks up rabbit and squirrel. Picks up a skin Hole 15 Everyting is running. Golfer A maintains rabbit as he also parred hole. He would have lost the rabbit had others had a lower score and he didn't Hole 16 Golfer G now knocks the rabbit and squirrel held by Golfer A from Hole 14 off. Rabbit and Squirrel now up for grabs. Picks up a skin Hole 17 The squirrel (birder or better) and rabbit are still 'loose". In this case multiple golfers birdied the hole. In this game you do not split rabbit or squirrel. Must be solo Hole 18 Golfer D wins a rabbit and squirrel. Not only did he birdie the hole, he also got lowest score and wins $66.66. Picks up a skin The skin pot is $66.66 to be split by the golfers who won a skin on a hole. Again, skins only apply if there are any rabbit and squirrel money left over. Those take priority. Golfer D wins 3 skins Golfer E wins one skin Golfer A wins 3 skins Golfer F wins one skin Golfer G wins 2 skins Golfer C wins one skin $66.66 divided by 11 skins paid to each above based on skin quantity What do I want What I want is a spreadsheet where I have the scores in one tab, then or it can be below the scores table / data is list the amount of money won for each golfer. Below I put the manual calculation for rabbit and squirrel. For skins, obviously populate the "per skin" amount and populate the cell accordingly from the example above Total Pot $200​ Total Rabbit Holes 1-6 Holes 7-12 Holes 13-18 Golfer A Golfer B Golfer C Golfer D 33.33 Golfer E Golfer F Golfer G Golfer H Golfer I Golfer J Total Squirrel Holes 1-6 Holes 7-12 Holes 13-18 Golfer A Golfer B Golfer C 33.33 Golfer D 33.33 Golfer E Golfer F 33.33 Golfer G Golfer H Golfer I Golfer J Total skins 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Golfer A Golfer B Golfer C Golfer D Golfer E Golfer F Golfer G Golfer H Golfer I Golfer J

 

[Rock5150]

Any help out there?…

 

[Johnny C]

Its complicated. I think you need to use a combination of IFS functions, MINIFS. You'd need a column for each hole. Say Row 4 col B,
=if(B4>$B$2,"", IF(B4=$B$2,"Rabbit", if(B4=minifs(B:B, B2),"Squirrel"))). That would tell you for each hole who got what. You'd then need an extra set of columns, for each hole for hole 3 which would be Column V, =if(V2>MINIFS(D:D.D2), "",AND(D4>$D$2,"", IF(D4=$D$2,"Rabbit"), if(D4=minifs(D:D,"Squirrel"))))
The parenthesis are probably ****ed up but you'll hopfully get the drift for each hole.
For the next bit you need a column that counts skins. Assuming the second set of columns is AA-AO, for each row "=COUNTIFS($AA2-$AO2,"?*") will tell you how many skins they won in each round. The sum of them will tell you how many to divide the whole pot for for each golfer for each round. These will go in AP:BG. You can then sum up the amount for each golfer, in BH2:BJ2 =SUM(AP2:AU2), =SUM(AV2:BA2), =SUM(BB2:BG2) and copy down.

My columns may not be right, but I hope you have something to start with.

Luckily I'm not a vegan. Do the winners get to eat bbqed the skinned bunnies and squirrels? I'm rubbish at golf (the best I ever scored was par+66, I ended up on several different holes) but i'd come to the bbq.

Hope that's of some use as a starter (or main course)

 

[Rock5150]

Its complicated. I think you need to use a combination of IFS functions, MINIFS. You'd need a column for each hole. Say Row 4 col B,
=if(B4>$B$2,"", IF(B4=$B$2,"Rabbit", if(B4=minifs(B:B, B2),"Squirrel"))). That would tell you for each hole who got what. You'd then need an extra set of columns, for each hole for hole 3 which would be Column V, =if(V2>MINIFS(D:D.D2), "",AND(D4>$D$2,"", IF(D4=$D$2,"Rabbit"), if(D4=minifs(D:D,"Squirrel"))))
The parenthesis are probably ****ed up but you'll hopfully get the drift for each hole.
For the next bit you need a column that counts skins. Assuming the second set of columns is AA-AO, for each row "=COUNTIFS($AA2-$AO2,"?*") will tell you how many skins they won in each round. The sum of them will tell you how many to divide the whole pot for for each golfer for each round. These will go in AP:BG. You can then sum up the amount for each golfer, in BH2:BJ2 =SUM(AP2:AU2), =SUM(AV2:BA2), =SUM(BB2:BG2) and copy down.

My columns may not be right, but I hope you have something to start with.

Luckily I'm not a vegan. Do the winners get to eat bbqed the skinned bunnies and squirrels? I'm rubbish at golf (the best I ever scored was par+66, I ended up on several different holes) but i'd come to the bbq.

Hope that's of some use as a starter (or main course)

Ok thanks for the input. Let me try it out. I know it’s complicated because of the “knocking” of and distributing unused funds. Yea we call it rabbit and squirrels but maybe I’ll call it spinach and carrots lol