Creating Balanced Teams (Golf)

[markko]

Sorry is this has been covered. I tried searching and found things were were, as they say, close, but no cigar.

In this case it's golf but I think the function can be more generally used, so I'll be more generic. And I'll use simple numbers as an example.

I have twelve players with various levels of skill. I need to divide them into 4 teams.

The easy part: They have been ranked from 1 to 12 based on their skill level. They have been assigned into 4 "Groupings" s using the CEILING function, so that the four highest ranked players are grouped together, the next four highest ranked together, etc.

What I would like to do is automatically create teams in which there is one player from each Grouping. IOW, each team would have a Grouping 1 player, a Grouping 2 player, a Grouping 3 player and a Grouping 4 player. It's kind of a "Choose one from Column A, one from Column B" kind of problem.

Any suggestions?

Thank you.

 

[maabadi]

How about Using Rand() and Rank.avg Function:

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1						Use Rank function to find rank of Random numbers
2		Use Rand() function to Create Random Numbers at 3 Column
3						Rank + 0	Rank + 4	Rank + 8
4	1	0.723715	0.394215	0.77794662		2	6	9
5	2	0.613843	0.735066	0.380120427		3	5	11
6	3	0.811797	0.201634	0.002644577		1	8	12
7	4	0.584097	0.305759	0.668585915		4	7	10
8
9
Sheet1

Cell Formulas
Range	Formula
B4:D7	B4	=RAND()
F4:F7	F4	=RANK.AVG(B4,B$4:B$7)
G4:G7	G4	=RANK.AVG(C4,C$4:C$7) + 4
H4:H7	H4	=RANK.AVG(D4,D$4:D$7) + 8

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	Player 1	1	1		Team 1	Player 3	Player 8	Player 12
3	Player 2	2	1		Team 2	Player 2	Player 6	Player 11
4	Player 3	3	1		Team 3	Player 1	Player 5	Player 9
5	Player 4	4	1		Team 4	Player 4	Player 7	Player 10
6	Player 5	5	2
7	Player 6	6	2
8	Player 7	7	2
9	Player 8	8	2
10	Player 9	9	3
11	Player 10	10	3
12	Player 11	11	3
13	Player 12	12	3
14
15		4
16
Master

Cell Formulas
Range	Formula
F2:F5	F2	=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=1),RANDARRAY($B$15))
G2:G5	G2	=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=2),RANDARRAY($B$15))
H2:H5	H2	=SORTBY(FILTER($A$2:$A$13,$C$2:$C$13=3),RANDARRAY($B$15))
C2:C13	C2	=CEILING.MATH(B2/$B$15,1)
Dynamic array formulas.

 

[Fluff]

A more dynamic option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Player	Rank	Group
2	Player 1	1	1		Team 1	Player 2	Player 5	Player 9
3	Player 2	2	1		Team 2	Player 4	Player 8	Player 12
4	Player 3	3	1		Team 3	Player 3	Player 7	Player 11
5	Player 4	4	1		Team 4	Player 1	Player 6	Player 10
6	Player 5	5	2
7	Player 6	6	2
8	Player 7	7	2
9	Player 8	8	2
10	Player 9	9	3
11	Player 10	10	3
12	Player 11	11	3
13	Player 12	12	3
14
15	Teams	4
16	Players	12
17
Master

Cell Formulas
Range	Formula
E2:E5	E2	="Team "&SEQUENCE(B15)
F2:H5	F2	=INDEX(SORTBY(A2:A13,C2:C13+RANDARRAY(B16)),MOD(SEQUENCE(B15,,0),B15)+SEQUENCE(,B16/B15,,B15))
C2:C13	C2	=CEILING.MATH(B2/$B$15,1)
Dynamic array formulas.

 

[markko]

Hi & welcome to MrExcel.
How about

They both work, Fluff. I even understand the formulas, although the first example much more than the second). I already had the ceiling formula to generate the initial Groupings. Since the number of players is variable and the number of players per team can vary, I will have to play around with both to see which works out over the course of a few weeks.

The only downside I see is that "RAND" recalculates every time the spreadsheet changes. I know the general workaround to copy and paste values once I like the array but is there a way to set up the sheet so that it only recalculates when I want it to?

 

[Fluff]

is there a way to set up the sheet so that it only recalculates when I want it to?

The only way to do that would be to turn calculation to manual. But that will affect all open workbooks & if you press F9 to recalculate then the teams will change.

 

[markko]

The only way to do that would be to turn calculation to manual. But that will affect all open workbooks & if you press F9 to recalculate then the teams will change.

Even that would work. Thank yo so much.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[chuckf201]

Fluff - I like your answer to this problem. I would like to extend the solution so that the teams have equal averages (in my case it's billiards handicaps)

 

[Fluff]

I would suggest that you start a new thread for this question.
Also what version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)