< Go Back

Randomly assign people to groups

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. In Excel, 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

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

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar