How to Generate Random Groups in Excel (Formula)

• Random Groups with Random Size (CHOOSE + RANDBETWEEN)
• Random Groups with Same Size (RAND + ROUND + RANK)

In both methods, we need to write a formula. And in this tutorial, we will learn both ways and understand them in detail.

In this example, you have a list of students with their names, and now you need to assign them a random group from north, south, east, and west.

Generating Random Groups in Excel

To write this formula, you can use the below steps:

1. First, in a cell, enter the CHOOSE function.
2. And in the first argument of the CHOOSE, which is index_num enter the RANDBETWEEN function.
3. Now, in the RANDBETWEEN, enter “1” as the bottom and “4” as the top. So you have four groups to get the result; that’s why you need to use 1 and 4 to create a range of random numbers.
4. Next, in the second argument of CHOOSE, enter the name of all four groups by using double quotation marks (“North”,”South”,”East”,”West”).
5. In the end, hit enter to get the result. And drag the formula up to the last name.

Note: RANBBETWEEN is a volatile function that updates itself when you change your worksheet.

=CHOOSE(RANDBETWEEN(1,4),"North","South","East","West")

How this Formula Works

To understand this formula, you need to split it into two parts: In the first part, we have RANDBETWEEN, which returns a random number between 1 to 4 (as we have four groups).

In the second part, we have CHOOSE function, which returns a value from the list you define using the index_number. When RANDBETWEEN returns a random number, CHOOSE returns the value from the list using that number.

When you have 3 in the index number, CHOOSE returns “East” in the result.

But there’s a Problem.

When you use this formula, there’s no same-size grouping. So you can see in the result that the groups assigned to the students are not of the same size.

This method is only proper when you don’t want to consider the group size; otherwise, you need to use the formula we will discuss next.

Generating Random Groups (Same Size)

To use this formula, you need to create a helper column with the RAND function to get the random number between 0 and 1, just like the following.

Note: RAND is also a volatile function that changes its value. And here, I’m going to convert the formula into values.

After that, enter a new column and the RANK function. Then, in the number argument, specify the random number from the B2; in the ref argument, use the entire range of random numbers.

=RANK(B2,$B$2:$B$13)

It creates a unique ranking for all the 12 students you have on the list. Now, you need to divide this ranking by three, as you need to have three students in a single group.

=RANK(B2,$B$2:$B$13)/3

Next, you need to use the ROUNDUP to round these rankings upwards.

=ROUNDUP(RANK(B2,$B$2:$B$13)/3,0)

After using ROUNDUP, you get an even size group where each group has the same number of students (12 students in the four groups with three students in each group). Then, again, use the CHOOSE to convert these number groups into group names.

=CHOOSE(ROUNDUP(RANK(B2,$B$2:$B$13)/3,0),"North","South","East","West")