Go Back

Randomly Assign People to Groups in Excel

At times we have to randomly assign people to groups. This may seem a mind-numbing task, but it can be made very easy when executed in Excel.

How to randomly assign people to groups in Excel

Here we can use a formula created by the Excel RANK and ROUNDUP functions together. We also use a helper column where RAND function is used to randomly generate values.

Formula

To randomly assign people to groups we use the formula

=ROUNDUP(RANK(A1,randoms)/size,0)

The above formula returns a group no. for each entry.

Here

randoms’ and ‘size’ are named range.

Random is a helper column generated by Excel RAND Function.

Explanation of formula

Excel RANK function is an inbuilt function in Excel which returns the rank of a number within a large set of numbers or array. This function is categorized as an inbuilt statistical function in Excel.

Excel ROUNDUP function is another inbuilt function in Excel which returns a number which is rounded up to a definite number of digits. This function rounds away from ‘0’. It is categorized as a Trig or Math function and can be aptly used as a worksheet function in Excel.

Example

Let us take an example; we have to assign random groups to students for the aerobics class. We provide the size of group 4 in G10 as shown. To create groups, we use the formula shown in D5.

=ROUNDUP(RANK(C5,random)/size,0)

Figure 1: Illustration of ROUNDUP and RANK formula

Here, “random” is the name of the range [C5:C20] and “size” is the named range[G10].

We can drag the formula and use the above formula for a hefty amount of data.

Figure 2: Randomly assign groups

We can also make use of the RAND function, which would generate random numbers. This function will keep on generating random values every time we change the values in the worksheet.

Alternate Solution

Another approach of assigning random value is by using the CEILING function which is another inbuilt function in Excel.

Formula

=CEILING(RANK(C5,random)/size,1)

Explanation of formula

This function can be used as an alternative to the ROUNDUP function. The ceiling function rounds up, but instead of rounding to some given number of decimal places, it rounds up to a given multiple.

Example

As per the example is shown in Figure 1, you can you use the above alternate formula to randomly assign people to groups as shown below.

Figure 3: Illustration of CEILING function

Figure 4: Result of applying CEILING function

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to randomly assign 164 people for 28 tables with no more than 6 at each table. Is there a formula for this?
Solved by I. W. in 23 mins
Hi! I need a formula to randomly assign a unique number to a cell from a range.
Solved by M. H. in 27 mins
Hi! I am trying to create groups in excel so I can later make a macro and have different students in them. For example I have 50 students and I want them in 5 groups, how can a randomly sort students in them? Basically I am trying to get a formula that will help me create teams randomly.
Solved by F. B. in 14 mins

Leave a Comment

avatar