Team Generator in excel
This blog article describes how to create teams randomly.
There are twenty names in column B and four teams in column E.
There will be five team members in each team.
In column C a team is randomly selected in each cell.
Array formula in cell C2:
=INDEX($E$2:$E$5, LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5))))) + CTRL + SHIFT + ENTER. Copy cell C2 and paste it down.
COUNTIF($C$1:C1, $E$2:$E$5)<5 makes sure that each team has max five team members.
ROW($1:$4) gives a row number to each team. Example, if you have three teams, change it to ROW($1:$3) otherwise the formula won´t work.
Download excel sample file for this tutorial
Team Generator.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
LARGE(array,k) returns the k-th largest row number in this data set.
ROW(reference) returns the rownumber of a reference
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
RANDBETWEEN(bottom, top)
Returns a random number between the numbers you specify
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
October 6th, 2010 at 5:03 am
1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so the maximum number of team members looks to a cell (say E9) so the number of people per team could be easily changed eg COUNTIF($C$1:C1, $E$2:$E$5)<E9
2 - In (virtual) sailing team racing, competition is usually 3 vs 3, but could be adjusted to a 4 vs 4, 3 vs 2 or 4 vs 3 depending on the number of skippers available to race. Could the example be modified to assign a skipper to a team given these optimum arrangements? (eg for say 12 skippers entered it would do 2 sets of 3 vs 3, for 8 skippers it would do one 4 vs 4, etc)
October 7th, 2010 at 9:54 pm
Mark G,
read this post: Dynamic team generator in excel