Excel allows us to create a random team generator using the **RAND, RANK **and** CEILING** functions. This step by step tutorial will assist all levels of Excel users to learn how to set up a random team generator in Excel.

* Figure 1. The final result of the formula*

**Syntax of the RAND Formula**

The generic formula for the RAND function is:

**=RAND()**

The function returns a random decimal number between 0 and 1 and has no parameters.

**Syntax of the RANK Formula**

The generic formula for the RANK function is:

**=RANK(number, ref, [order])**

The parameters of the RANK function are:

**number**– a number which we want to rank**ref**– a list of numbers for ranking**[order] – 1 for ascending or 0 for descending order. This parameter is optional, if it’s omitted, the default order is descending.**

**Syntax of the CEILING Formula**

The generic formula for the CEILING function is:

**=CEILING(number, significance)**

The parameters of the RANK function are:

**number**– a number which we want to round up to the nearest significance**significance – a significance for rounding a number up.**

**Setting up Our Data for the Formula**

Let’s look at the data that we will use in the example. To group names into teams, we will need to have several helper columns. In column B (“Names”), we have names of team members. In column C (“Random”), we will have a random number 0-1 for every name. In column D (“Rank”), we will get a rank for every random number. In column E (“Grouping”), we’ll divide the rank with 3, as we have 3 teams. Finally, in column F (“Team”), we’ll get the team (1, 2 or 3) for each name

* Figure 2. The data structure that we will use in the example*

**Set up a Random Team Generator**

First, we need to get a random number in column C for each name.

The formula for RAND in C3 looks like:

**=RAND()**

To apply the formula, we need to follow these steps:

- Select cell C3 and click on it
- Insert the formula:
`=RAND()`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

* Figure 3. Using the RAND formula *

When we get a random number for each name we can rank them in column D.

The formula for RANK in C3 looks like:

**=RANK(C3, $C$3:$C$14)**

The **number** parameter is the cell C3. The **ref** parameter is the range $C$3:$C$14. We must fix the range, as it’s not changing when the formula is copied down the cells.

To apply the formula, we need to follow these steps:

- Select cell D3 and click on it
- Insert the formula:
`=RANK(C3,$C$3:$C$14)`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

* Figure 4. Using the RANK formula *

Now we have the rank for every random number in column D and can divide them by 3.

The formula in E3 looks like:

**=RANK/D3**

To apply the formula, we need to follow these steps:

- Select cell E3 and click on it
- Insert the formula:
`=RANK/D3`

- Press enter
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

* Figure 5. Using the formula for grouping *

Finally, we can use the CEILING formula, to assign team 1, 2 or 3 to each name.

The formula for CEILING in F3 looks like:

**=CEILING(E3, 1)**

The parameter number is the cell E3, while the significance is 1.

To apply the formula, we need to follow these steps:

- Select cell F3 and click on it
- Insert the formula:
`=CEILING(E3,1)`

- Press enter

*Figure 6. Using the CEILING formula *

As you can see in Figure 6, in “Team” column we have assigned a team for each name from column B.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment