Golf Two Man Low Net Score Game-Calculate Payout for top 3 teams including team ties

[Golfhacker]

There are six 2man teams. Payout is $70 for team 1 ($35 each player), $50 for team 2($25 each player), and $30 for team 3 ($15 for each player). I rank the net score for each team, then assign the rank number for the two players on that team.
For example, Team 1 (John C and Rick D)score is 68 Team 4 (TimH and RickP) score is 69 and Team 6 (BillW and DaveF) score is 70. The four players on Team 1 and 2 would split $120 or $30 for each player.
Team 1 rank is 1; John C and Rick D rank is 1
Team 4 rank is 2; TimH and RickP rank is 2
Team 6 rank is 3; BillW and DaveF rank is 3

I'd like the Summary worksheet to look as follows:
Rank Score Payout
John C 1 68 35
Rick D 1 68 35
TimH 2 69 25
RickP 2 69 25
BillW 3 70 15
DaveF 3 70 15

Also. how do I deal with team ties for the payout and assign the correct payout for each player?
Thank you.

 

[Drrellik]

Book1
	A	B	C	D	E	F	G	H
1	Players			Team 1		Team Score	Team Rank	Team Prize
2	1	Adam		1	Adam	75	6
3	2	Bob		4	Donald
4	3	Charles
5	4	Donald		Team 2
6	5	Eric		3	Charles	69	2	$25
7	6	Fred		7	Greg			$25
8	7	Greg
9	8	Hugh		Team 3
10	9	Little Ricky		5	Eric	63	1	$35
11	10	James		6	Fred			$35
12	11	Kevin
13	12	Leroy		Team 2
14				10	James	71	4
15				12	Leroy
16
17				Team 2
18				8	Hugh	69	2	$25
19				11	Kevin			$25
20
21				Team 2
22				2	Bob	73	5
23				9	Little Ricky
week 1

Cell Formulas
Range	Formula
E22:E23,E18:E19,E14:E15,E10:E11,E6:E7,E2:E3	E2	=VLOOKUP(D2,$A$2:$B$13,2)
G2,G22,G18,G14,G10,G6	G2	=RANK(F2,$F$2:$F$23,1)
H2,H22,H18,H14,H10,H6	H2	=IF(G2>3,"",IF(G2=3,"$15",IF(G2=2,"$25",IF(G2=1,"$35",""))))
H3,H23,H19,H15,H11,H7	H3	=IF(G2>3,"",IF(G2=3,"$15",IF(G2=2,"$25",IF(G2=1,"$35",""))))

Not sure if this is what you are looking for but if you rank the teams from 1 to 6 and have a tie, rank will set the values as 1,2,2,4,5,6 and the payout could simply be adjusted to pay both teams the extra $20 total since the difference would be 25 & 25 vs 25 & 15 (each player)

Additionally you may want to track each players winning totals over the season

HTH ~DR

 

[Golfhacker]

Book1
	A	B	C	D	E	F	G	H
1	Players			Team 1		Team Score	Team Rank	Team Prize
2	1	Adam		1	Adam	75	6
3	2	Bob		4	Donald
4	3	Charles
5	4	Donald		Team 2
6	5	Eric		3	Charles	69	2	$25
7	6	Fred		7	Greg			$25
8	7	Greg
9	8	Hugh		Team 3
10	9	Little Ricky		5	Eric	63	1	$35
11	10	James		6	Fred			$35
12	11	Kevin
13	12	Leroy		Team 2
14				10	James	71	4
15				12	Leroy
16
17				Team 2
18				8	Hugh	69	2	$25
19				11	Kevin			$25
20
21				Team 2
22				2	Bob	73	5
23				9	Little Ricky
week 1

Cell Formulas
Range	Formula
E22:E23,E18:E19,E14:E15,E10:E11,E6:E7,E2:E3	E2	=VLOOKUP(D2,$A$2:$B$13,2)
G2,G22,G18,G14,G10,G6	G2	=RANK(F2,$F$2:$F$23,1)
H2,H22,H18,H14,H10,H6	H2	=IF(G2>3,"",IF(G2=3,"$15",IF(G2=2,"$25",IF(G2=1,"$35",""))))
H3,H23,H19,H15,H11,H7	H3	=IF(G2>3,"",IF(G2=3,"$15",IF(G2=2,"$25",IF(G2=1,"$35",""))))

Not sure if this is what you are looking for but if you rank the teams from 1 to 6 and have a tie, rank will set the values as 1,2,2,4,5,6 and the payout could simply be adjusted to pay both teams the extra $20 total since the difference would be 25 & 25 vs 25 & 15 (each player)

Additionally you may want to track each players winning totals over the season

HTH ~DR

Thank you for the reply. I understand what you've provided. I was trying to automate the payout calculation with ties for the two-man game and I don't see an easy method. Using your formulas will be an option

 

[Drrellik]

what would the new values be on a tie? if all three teams tie for first place do they all split 150 150/6

 

[Drrellik]

golf.xlsx
	A	B	C	D	E	F	G	H
1	Players			Team 1		Team Score	Team Rank	Team Prize
2	1	Adam	1	1	Adam	63	2	25
3	2	Bob	2	4	Donald
4	3	Charles
5	4	Donald	1	Team 2
6	5	Eric	1	3	Charles	63	2	25
7	6	Fred	1	7	Greg
8	7	Greg
9	8	Hugh		Team 3
10	9	Little Ricky		5	Eric	64	4
11	10	James		6	Fred
12	11	Kevin
13	12	Leroy		Team 2
14				10	James	62	1	70
15		Total		12	Leroy
16
17				Team 2
18				8	Hugh	69	6
19				11	Kevin
20
21				Team 2
22				2	Bob	68	5
23				9	Little Ricky
week 1

Cell Formulas
Range	Formula
G2,G22,G18,G14,G10,G6	G2	=RANK(F2,$F$2:$F$23,1)
H2,H22,H18,H14,H10,H6	H2	=IF(G2=1,(70/VLOOKUP(G2,$A$2:$C$7,3)),IF(G2=2,(50/VLOOKUP(G2,$A$2:$C$7,3)),IF(G2=3,(30/VLOOKUP(G2,$A$2:$C$7,3)),"")))
E22:E23,E18:E19,E14:E15,E10:E11,E6:E7,E2:E3	E2	=VLOOKUP(D2,$A$2:$B$13,2)
C2	C2	=COUNTIFS($G$2:$G$23,1)
C3	C3	=COUNTIFS($G$2:$G$23,2)
C4	C4	=COUNTIFS($G$2:$G$23,3)
C5	C5	=COUNTIFS($G$2:$G$23,4)
C6	C6	=COUNTIFS($G$2:$G$23,5)
C7	C7	=COUNTIFS($G$2:$G$23,6)

I added a helper cell in column C to count the number of "ties' and modified the formula


Maybe this can help or get you to where you are going.

Don

 

[Golfhacker]

golf.xlsx
	A	B	C	D	E	F	G	H
1	Players			Team 1		Team Score	Team Rank	Team Prize
2	1	Adam	1	1	Adam	63	2	25
3	2	Bob	2	4	Donald
4	3	Charles
5	4	Donald	1	Team 2
6	5	Eric	1	3	Charles	63	2	25
7	6	Fred	1	7	Greg
8	7	Greg
9	8	Hugh		Team 3
10	9	Little Ricky		5	Eric	64	4
11	10	James		6	Fred
12	11	Kevin
13	12	Leroy		Team 2
14				10	James	62	1	70
15		Total		12	Leroy
16
17				Team 2
18				8	Hugh	69	6
19				11	Kevin
20
21				Team 2
22				2	Bob	68	5
23				9	Little Ricky
week 1

Cell Formulas
Range	Formula
G2,G22,G18,G14,G10,G6	G2	=RANK(F2,$F$2:$F$23,1)
H2,H22,H18,H14,H10,H6	H2	=IF(G2=1,(70/VLOOKUP(G2,$A$2:$C$7,3)),IF(G2=2,(50/VLOOKUP(G2,$A$2:$C$7,3)),IF(G2=3,(30/VLOOKUP(G2,$A$2:$C$7,3)),"")))
E22:E23,E18:E19,E14:E15,E10:E11,E6:E7,E2:E3	E2	=VLOOKUP(D2,$A$2:$B$13,2)
C2	C2	=COUNTIFS($G$2:$G$23,1)
C3	C3	=COUNTIFS($G$2:$G$23,2)
C4	C4	=COUNTIFS($G$2:$G$23,3)
C5	C5	=COUNTIFS($G$2:$G$23,4)
C6	C6	=COUNTIFS($G$2:$G$23,5)
C7	C7	=COUNTIFS($G$2:$G$23,6)

I added a helper cell in column C to count the number of "ties' and modified the formula


Maybe this can help or get you to where you are going.

Don

Thank you. I wasn't sure if I am allowed to upload my complete workbook. For a future golf outing, we are playing 18 hole individual low net, skins, plus a two-man low net game. With help from you and others online, all I have to do is enter each person's gross score for each hole and everything is calculated. I use helper rows and columns and some brute force formulas. I found a good method to deal with ties (for example: =AVERAGE(OFFSET($G$40,F6,0,COUNTIF($F$6:$F$17,F6)))) and distribute prize money accordingly.