How to randomly assign people to teams

Here we have a list of 36 people.

This is a great way to solve more complicated problems in Excel.

I’ll start with anExcel 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 theRAND functionto get a random number for each person.

RAND generates small numbers between 0 and 1.

RAND is avolatile function, so it will recalculate with every worksheet change.

We don’t want that behavior, so I’ll usePaste Specialto convert the formulas to values.

Next, I’ll use theRANK functionto rank each person according to their random number.

RANK needs a number, and a list of numbers to rank against.

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 theCEILING 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 see to it this is working right, I’ll use theCOUNTIF functionto count team members.

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

Now when I change the team size, everything still works.

Finally, I’ll consolidate formulas.

First I’ll copy in the grouping formula.

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 theRAND function.