Dynamic team generator in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Random > Dynamic team generator in excel

← Previous post -

Filed in Excel, Random on Oct.07, 2010. Email This article to a Friend

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)

Answer:

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

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

Team Generator in excel

Create a random playlist in excel

Find the largest number in a dynamic column using excel formula

Prevent duplicates using dynamic conditional formatting in excel

Create a random playlist in excel – True round-robin tournament