# How to build a Team Generator – different number of people per team

Hi, what if we have different number of people per team? So in team A, there could be a max of 4 members but in team B there could be a max of 6 members etc. Is there some formula which can be used for this as well?

Yes, there is. This array formula uses the values in cell range D2:E4 to randomly assign people to teams, demonstrated in column B and C.

#### What's on this webpage

## 1. Team Generator (Formula)

The image above shows a formula in column D that assigns teams to names randomly based on the number of team members specified in column G.

Change the team names in column F if you like and type a number in the corresponding cell in column G to force the formula to assign a maximum number of team members.

Array formula in cell D3:

This array formula contains a couple of excel functions, if you want to know more about them check out these posts: LARGE, INDEX, COUNTIF, IFERROR, MATCH, ROW, RANDBETWEEN. It also has both absolute and relative cell references, learn more.

## 2. How to enter an array formula

- Copy above array formula
- Select cell C2
- Paste array formula to the formula bar
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once

If you did it right the formula now begins and ends with curly brackets, like this {=array formula}. Don't enter the curly brackets yourself.

## 3. How to copy the formula to cells below

- Select cell C2
- Copy cell (Shortcut key CTRL + c)
- Select cell range C3:C21
- Paste (Shortcut key CTRL + v)

You can also select cell C2, press and hold on the black dot on the lower right corner of cell C2. Drag it down as far as needed.

## 4. Named ranges in the array formula

There are two named ranges in this worksheet, *Teams* and *Members*. They return a cell range in column F and G respectively that grow automatically as you add new values.

### 4.1 How to create a named range

- Copy formula below.
- Go to tab Formula on the ribbon.
- Press with left mouse button on the "Name Manager" button. A dialog box appears.
- Press with left mouse button on the "New..." button located on the dialog box. Another dialog box appears.
- Paste formula to "Refers to;" field.
- Name the named range in "Name:" field.
- Press with left mouse button on OK button.
- Repeat steps 4 to 7 to create the second named range.
- Press with left mouse button on OK button on the first dialog box to apply changes and dismiss it.

Teams:

Members:

## 5. Explaining formula in cell D3

IFERROR(INDEX(Teams, LARGE((COUNTIF($D$2:D2, Teams)<>Members)*MATCH(ROW(Teams), ROW(Teams)), RANDBETWEEN(1, SUM(--(COUNTIF($D$2:D2, Teams)<>Members))))), "")

#### Step 1 - Count previous teams in cells above

The COUNTIF function calculates the number of cells that are equal to a condition.

COUNTIF(*range*, *criteria*)

The first argument is a cell reference that contains both a relative and absolute part meaning the cell ref grows when cell is copied to cells below.

The second argument is a dynamic named range meaning it grows when more values are added.

COUNTIF($D$2:D2, Teams)

becomes

COUNTIF("Team", {"Team A"; "Team B"; "Team C"; "Team D"})

and returns {0; 0; 0; 0}.

#### Step 2 - Check if value is not equal to the total number of members in a team

The less than and larger than character combined means not equal to. This step makes sure that there is a correct number of assigned team members.

COUNTIF($D$2:D2, Teams)<>Members

becomes

{0;0;0;0}<>Members

becomes

{0;0;0;0}<>{4;6;3;7}

and returns {TRUE; TRUE; TRUE; TRUE}

#### Step 3 - Convert boolean values to numerical

This step converts TRUE to 1 and FALSE to 0 (zero).

--(COUNTIF($D$2:D2, Teams)<>Members)

becomes

--({TRUE; TRUE; TRUE; TRUE})

and returns {1; 1; 1; 1}. This step is needed to be able to calculate a sum.

#### Step 4 - Calculate total

The SUM function adds numbers from a cell range or an array and returns a total. It can't, however, sum boolean values.

SUM(--(COUNTIF($D$2:D2, Teams)<>Members))

becomes

SUM({1; 1; 1; 1})

and returns 4.

#### Step 5 - Create a random number based on Team member count

The RANDBETWEEN function returns a random whole number between the numbers you specify.

RANDBETWEEN(bottom, top)

RANDBETWEEN(1, SUM(--(COUNTIF($D$2:D2, Teams)<>Members))))

becomes

RANDBETWEEN(1, 4)

and returns a pseudo-random number from 1 to 4.

#### Step 6 - Create an array from 1 to n

The ROW function returns the row number from a cell reference, if a cell range is used all numbers are returned in an array.

MATCH(ROW(Teams), ROW(Teams))

becomes

MATCH({3;4;5;6}, {3;4;5;6})

The MATCH function finds the relative position of a given value in a cell range or array. This step makes sure the array begins with 1.

MATCH({3;4;5;6}, {3;4;5;6})

returns {1;2;3;4}.

#### Step 7 - Extract k-th largest value

The LARGE function extracts the k-th largest number from an array or cell range.

LARGE(array, k)

LARGE((COUNTIF($D$2:D2, Teams)<>Members)*MATCH(ROW(Teams), ROW(Teams)), RANDBETWEEN(1, SUM(--(COUNTIF($D$2:D2, Teams)<>Members))))

becomes

LARGE((COUNTIF($D$2:D2, Teams)<>Members)*MATCH(ROW(Teams), ROW(Teams)), 3)

becomes

LARGE(({TRUE; TRUE; TRUE; TRUE})*MATCH(ROW(Teams), ROW(Teams)), 3)

becomes

LARGE(({TRUE; TRUE; TRUE; TRUE})*{1;2;3;4}, 3)

becomes

LARGE(({TRUE; TRUE; TRUE; TRUE})*{1;2;3;4}, 3)

becomes

LARGE({1;2;3;4}, 3)

and returns 2.

#### Step 8 - Get value

The INDEX function returns a value from a cell range based on a row and column number (optional).

INDEX(Teams, LARGE((COUNTIF($D$2:D2, Teams)<>Members)*MATCH(ROW(Teams), ROW(Teams)), RANDBETWEEN(1, SUM(--(COUNTIF($D$2:D2, Teams)<>Members)))))

becomes

INDEX(Teams, 2)

becomes

INDEX({"Team A";"Team B";"Team C";"Team D"}, 2)

and returns "Team B" in cell D3.

#### Step 9 - Check if value is an error

The IFERROR function handles errors, you can specify what value to returns if an error is found.

IFERROR(*value*, *value_if_error*)

IFERROR(INDEX($F$3:$F$19, LARGE((COUNTIF($D$2:D2, Teams)<>Members)*MATCH(ROW(Teams), ROW(Teams)), RANDBETWEEN(1, SUM(--(COUNTIF($D$2:D2, Teams)<>Members))))), "")

becomes

IFERROR("Team B", "")

and returns "Team B".

## 6. Disable automatic recalculation

The RANDBETWEEN function is volatile and each time you enter a new value somewhere on this sheet, array formulas are recalculated.

To prevent this from happening the following event code turns off automatic calculation. Activating sheet1 enables manual calculation, deactivating sheet1 enables automatic calculation.

Private Sub Worksheet_Activate() Application.Calculation = xlCalculationManual End Sub Private Sub Worksheet_Deactivate() Application.Calculation = xlCalculationAutomatic End Sub

**Where is this code? **Press with right mouse button on on tab sheet1. Press with mouse on "View Code..."

Remember to press F9 after editing teams and member columns.

Want to learn more about advanced excel techniques, join my Advanced excel course.

These posts use the same technique:

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

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

### 5 Responses to “How to build a Team Generator – different number of people per team”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

In a scenario like this and recalculating the setup several times, I see that the Team A is never assigned to #10 or greater. Is this a limitation?

Felipe Costa Gualberto,

This workbook assigns teams evenly.

https://www.get-digital-help.com/wp-content/uploads/2015/04/Team-Generatorv2.xlsm

I'm trying to upload the image, but I can't.

The link would be: https://s24.postimg.org/iy5s86r8l/Sem_t_tulo.png

However, the setup I did was just 1 member in Team A and 19 members in Team B.

Felipe Costa Gualberto,

If there are two teams, the probability is 50% that a team is displayed in a cell, that is why Team B is shown almost always in one of the three or four first cells. This is perhaps not ideal, I am working on a better formula.

Hi Oscar, how would you do this with an additional level of criteria? For example, if your participants are also in categories: A, B & C and in Group 1 you want 3 Category A participants, 2 Category B, etc.