Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# 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.