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