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)))))

Team generator

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

The following shows you how to setup a dynamic scoreboard, enter results and the scoreboard is automatically recalculated:

Dynamic scoreboard

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

### How to create an array formula

1. Copy the aray formula above (Ctrl + c)
2. Double click cell B2
3. Paste (Ctrl + v)
4. Press and hold Ctrl + Shift simultaneously
5. Press Enter
6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=array_formula}

Don't enter these characters yourself, they appear automatically.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

### How to copy formula

Copy cell C2 and paste it down.

### Customize formula

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.

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

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

SMALL function and LARGE function

This function lets you extract any number in a cell range based on sort rank.

INDEX function explained

Fetch a value in a data set based on coordinates.

Excel’s RAND function

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]