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

This article demonstrates how to generate teams with different sizes:

Team generator

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

Comments(4) Filed in category: Excel, Random, Team generator

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 […]

Comments(7) Filed in category: Count values, Excel, MMULT function, SUM function

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.

Comments(2) Filed in category: Count values, Excel

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.

Download excel sample file for this tutorial

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

Functions in this article:

How to use Excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Comments(2) Filed in category: Excel, SUMPRODUCT function

COUNTIF function

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

Comments(5) Filed in category: Excel

SMALL function and LARGE function

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

Comments(12) Filed in category: Excel

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Excel RANDBETWEEN and RAND functions – How to generate random numbers and text

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

Comments(10) Filed in category: Excel