To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. In the example shown, the formula in D5 is:
which returns a group number for each name listed in column B, where "randoms" is the named range C5:C16, and "size" is the named range G5.
How this formula works
At the core of this solution is the RAND function, which is used to generate a random number in a helper column (column C in the example).
To assign a full set of random values in one step, select the range C5:C16, and type =RAND() in the formula bar. Then use the shortcut control + enter to enter the formula in all cells at once.
Note: the RAND function will keep generating random values every time a change is made the worksheet, so typically you will want to replace the results in column C with actual values using paste special to prevent changes after random values are assigned.
In column D, a group number is assigned with the following formula:
The RANK function is used to rank the value in C5 against all random values in the list. The result will be a number between 1 and the total number of people (12 in this example).
This result is then divided by "size", which represents the desired group size (3 in the example), which then goes into the ROUNDUP function as number, with num_digits of zero. The ROUNDUP function returns a number rounded up to the next integer. This number represents assigned group number.
The CEILING function can be used instead of ROUNDUP. Like the ROUNDUP function, CEILING also rounds up but instead of rounding to a given number of decimal places, CEILING rounds to a given multiple.
To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F3 is: = CHOOSE ( RANDBETWEEN ( 1 , 3 ), "A" , "B" , "C" ) When copied down...
To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in C5 is: = CHOOSE ( RANDBETWEEN ( 1 , 4 ), "Red" , "Blue" , "...
To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. In the example shown, the formula in B4 is: = CHOOSE ( RANDBETWEEN ( 1 , 4 ),...
The Excel CEILING function returns a given number rounded up to a specified multiple. For example, =CEILING(A1,5) could be used to round a price in A1 to the nearest 5 dollars. CEILING always rounds up.
In this video, we look at a step by step approach to randomly assign people to groups with teams using formulas. Along the way, we use three functions (RAND, RANK, and CEILING), helper columns, and nesting. We verify results with the COUNTIF...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.