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.
Leave a Comment