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:
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:
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
LARGE(array,k) 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
Returns a random number between the numbers you specify