How to randomly assign people to teams

In this video we’ll look at a basic way to randomly assigning people teams.

Here we have a list of 36 people.

Let’s say we want to randomly assign each person to a team of 4 people, so that we have a total of 9 with 4 people in each.

I’m going to solve this problem in small steps, with helper columns, then bring things together in the end. This is a great way ot solve more complicated problems in Excel.

I’ll start off with an Excel Table, to make the formulas very fast to enter.

Then, I’ll add columns for Rand, rank, grouping and Team number. The purpose of each column will become clear as we go along.

Next, I’ll use the RAND function to assign a random number to each person. RAND generates small numbers between zero and 1.

RAND is a volatile function, so it will recalculate with every worksheet change. We don’t want that behavior, so I’ll use paste special to convert the formulas to values.

Next, I’ll use the RANK function to rank each person according to their random number. RANK needs the number, and a list of numbers to rank against.

The result is a list of numbers between 1 and 36, where 1 represents the largest value, and 36 represents the smallest.

We’re getting close.

We just need a way to group by rank.

I’ll do this by dividing rank by the team size, which is 4.

This produces some messy numbers, but, we now have what we need.

If we round these numbers up, we’ll have team numbers between 1 and 9. This is a perfect job for the CEILING function, which rounds up to a given multiple.

I need to give CEILING the number, and specify a multiple of 1, and we have our teams.

Now, to make sure this is working right, I’ll use the COUNTIF function to count team members.

Next, I’ll replace the hard-coded team size with a reference.

RANK([@rand],[rand])/$F$5Now when I change the team size, everything still work.

Finally, I’ll consolidate formulas.

First I’ll copy in the grouping formula.

=CEILING(@rank]/$F$5,1)

Next I’ll copy in the rank formula.

Now I can delete the two helper columns.

To generate new teams at any time, I can again use the RAND function.