Download Worksheet (25.11 KB)
To randomly assign rows of data to arbitrary groups, you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F5 is:
=CHOOSE(RANDBETWEEN(1,3),"A","B","C") As the formula is copied down the column, it will return a random group ("A", "B", or "C") at each new row.
Note: this approach will create groups of different sizes. If you need to assign random groups with a fixed size, see this formula.
=CHOOSE(RANDBETWEEN(1,3),"A","B","C")In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below.
The CHOOSE function returns a value from a list of values using an index number. The index number is provided as the first argument, and the values to be selected follow. For example, if we have a list of three colors ("red", "blue", and "green"), we can configure CHOOSE to return each color in turn with the following formulas:
CHOOSE(1,"red","blue","green") // returns "red" CHOOSE(2,"red","blue","green") // returns "blue" CHOOSE(3,"red","blue","green") // returns "green"Notice that CHOOSE uses the index number to select the "nth" value from the list of values. The values can be customized in any way you like and the only requirement is that the index number be valid for the number of values provided. Of course, in this example, we don't want to hardcode an index number into CHOOSE, we want a random index number. For this, we can use the RANDBETWEEN function.
The RANDBETWEEN function generates a random number between two integers, provided as the bottom and the top. For example, to generate a random number between 1 and 10, you can use RANDBETWEEN like this:
=RANDBETWEEN(1,10) // returns a random number between 1 and 10When Excel's calculation engine updates a worksheet, RANDBETWEEN will generate a random number between 1 and 10.
The behavior of RANDBETWEEN will work perfectly for this problem. We have three possible groups ("A","B","C") so we need a random number between 1 and 3, which we can get like this:
=RANDBETWEEN(1,3) // returns a random number between 1 and 3The final step is to embed RANDBETWEEN into the CHOOSE function as the index number like this:
=CHOOSE(RANDBETWEEN(1,3),"A","B","C")This is the formula that appears in cell F5 in the example shown. When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3. This number is delivered directly to the CHOOSE function as the index number, and CHOOSE returns the corresponding color as a final result. You can use this approach whenever you need to assign random text values to each row in a data set. Just be sure to adjust the second argument in RANDBETWEEN, top, to match the number of values provided.
Be aware that RANDBETWEEN is a volatile function and will recalculate whenever there is any change to a workbook, or even when a workbook is opened. To force a recalculation, you can press the F9 key. Once you have a set of random assignments, you may want to stop the formula from returning new results. The classic way to do this is to use Paste Special:
After you press OK, all formulas will be replaced with static values.
In the current version of Excel (Excel 2021 or later) you can use a single dynamic array formula to generate all random values at once. One option is to use the RANDARRAY function with CHOOSE like this:
=CHOOSE(RANDARRAY(ROWS(B5:B104),,1,3,TRUE),"A","B","C")The core idea of this formula is the same as the original formula above. However, instead of RANDBETWEEN, we use RANDARRAY, which can generate an array of random numbers in one step. To figure out how many random numbers to generate, we use the ROWS function on a range corresponding to the first column of the data. This saves us the step of telling RANDARRAY how many rows we need. In this case, ROWS returns 100, because there are 100 rows in the range B5:B104. Simplifying, we now have:
=CHOOSE(RANDARRAY(100,,1,3,TRUE),"A","B","C")Next, RANDARRAY generates an array of 100 random numbers between 1 and 3. The result is returned to CHOOSE as the index_num argument, and CHOOSE uses the random numbers to return an array that contains 100 random groups. This array lands in cell F5 and spills into the range F5:F104.
RANDARRAY is a volatile function and will recalculate with each worksheet change.It is also possible to use the INDEX function instead of CHOOSE in a formula like this:
=INDEX(,RANDBETWEEN(1,3))Like CHOOSE, INDEX retrieves a value based on an index number. INDEX however accepts the values all at once in the first argument, called array. In the formula above, the values "A", "B", and "C" are provided as an array constant to INDEX as the array, and RANDBETWEEN is used as before to generate a random number between 1 and 3. The RANDARRAY version of the formula with INDEX looks like this:
=INDEX(,RANDARRAY(ROWS(B5:B104),,1,3,TRUE))One advantage of INDEX is that the array constant can be replaced with a range on the worksheet. In other words, you can enter group names into a range and provide that range to INDEX. The CHOOSE function will not accept a range of values; it requires that values be provided separately.
Note: the formulas on this page will create completely random groups. One result is that the total number of rows assigned to each group will vary. If you need to assign random groups with a fixed size (i.e. randomly assign people to teams of 6), see the example on this page.
In this example, the goal is to randomly assign the names in column B to three groups of equal size. The group names are "A", "B", and "C", and these values appear in the named range groups (F5:F7). The solution should automatically count the number of groups to assign and attempt to generate the.
The Excel RANDBETWEEN function returns a random integer between given numbers. In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is random numbers between 1-100. RANDBETWEEN is a volatile function that recalculates when a.
The RANDBETWEEN function takes two numbers, a bottom and top number, and generates a random integer in between. Dates in Excel are serial numbers, so you can use the DATE function to create the lower number and the upper number. RANDBETWEEN then generates a number that falls between these two date.
The CHOOSE function provides the framework for this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index. In this case, we are using four values.
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead. To pull a random value out of a list or table, we'll need a random row number. For that, we'll use the RANDBETWEEN function, which.
The CHOOSE function does most of the work in this formula. Choose takes a single numeric value as its first argument (index_number), and uses this number to select and return one of the values provides as subsequent arguments, based on their numeric index. In this case, we are providing four.
