The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function.
Both approaches are explained below.
The CHOOSE function
TheCHOOSE functionreturns 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 this, we can use the RANDBETWEEN function.
The RANDBETWEEN function
TheRANDBETWEEN functiongenerates a random number between two integers, provided as thebottomand thetop.
CHOOSE with RANDBETWEEN
The behavior of RANDBETWEEN will work perfectly for this problem.
When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3.
To force a recalculation, you’ve got the option to press the F9 key.
However, instead of RANDBETWEEN, we use RANDARRAY, which can generate anarrayof random numbers in one step.
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.
This array lands in cell F5 andspillsinto the range F5:F104.
INDEX however accepts the values all at once in the first argument, calledarray.
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 it’s crucial that you assign random groups with a fixed size (i.e.
randomly assign people to teams of 6), see the example onthis page.
RANDBETWEEN recalculates each time a worksheet is opened or changed.
The values provided to CHOOSE can include references.
RANDARRAY Function
The Excel RANDARRAY function generates an array of random numbers between two values.
The size or the array is specified byrowsandcolumnsarguments.
The generated values can be either decimals or whole numbers.
ROWS Function
The Excel ROWS function returns the count of rows in a given reference.
