Author: Oscar Cronquist Article last updated on January 24, 2018

Mark G asks:

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)


Array formula in cell C2:

=INDEX(OFFSET($D$2:$D$5, 0, 0, $F$1), LARGE((COUNTIF($C$1:C1, OFFSET($D$2:$D$5, 0, 0, $F$1))<$F$2)*(ROW(OFFSET($A$1, 0, 0, $F$1))), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, OFFSET($D$2:$D$5, 0, 0, $F$1))<$F$2))))) + CTRL + SHIFT + ENTER.

Copy cell C2 and paste it down as far as necessary.

Change values in cell F1 and F2 to create a different team setup.

Download excel sample file for this tutorial

Dynamic Team Generator.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

Counts the number of cells within a range that meet the given condition

Returns the k-th largest row number in this data set.

ROW(reference) returns the rownumber of a reference

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