Excel formula: Randomly assign data to groups

Generic formula

=CHOOSE(RANDBETWEEN(1,3),"Group A","Group B","Group B")

Summary

To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F3 is:

=CHOOSE(RANDBETWEEN(1,3),"A","B","C")

When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list.

Note: this is a random approach that will allow groups of difference sizes. If you need a formula approach to randomly assign people to groups of a fixed size, see this formula and this video overview.

Explanation

The RANDBETWEEN function generates random numbers between two integers. The CHOOSE function takes a number as the first argument, and uses that number to select the "nth" item from the following arguments.

So, in this formula, RANDBETWEEN generates a number between 1 and 3, this number is used to choose a group from the 3 following values: "A","B","C".

You can use this same approach any time you need make random assignments. It's especially useful when you you need to assign data to a limited number of text values.

Automatic recalculation

Be aware that RANDBETWEEN will re-calculate whenever there is any change to a workbook, or even when a workbook is opened. Once you have a set of random assignments, you may want to copy and paste the formulas as values to prevent further changes.

Author

Dave Bruns

Related formulas

Randomly assign people to groups

At the core of this solution is the RAND function, which is used to generate a random number in a helper column (column C in the example). To assign a full set of random values in one step, select the range C5:C16, and type =RAND() in the formula...

Random number between two numbers

To generate random integers between two numbers, you can use the RANDBETWEEN function. 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...

Random date between two dates

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 t he lower number and the upper number. RANDBETWEEN then...

Random text values

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

Random value from list or table

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

Random number from fixed set of options

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

Related functions

Excel RANDBETWEEN Function

The Excel RANDBETWEEN function returns a random integer between given numbers. RANDBETWEEN recalculates when a worksheet is opened or changed.

Excel CHOOSE Function

The Excel CHOOSE function returns a value from a list using a given position or index. For example, CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can...

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.