RANDARRAY function - quick way to generate random numbers in Excel

RANDARRAY function - things to remember

To efficiently generate random numbers in your Excel worksheets, there are 6 important points to take notice of:

• The RANDARRAY function is only available in Excel for Microsoft 365 and Excel 2021. In Excel 2019, Excel 2016 and earlier versions the RANDARRAY function is not available.
• If the array returned by RANDARRAY is the final result (output in a cell and not passed to another function), Excel automatically creates a dynamic spill range and populates it with the random numbers. So, be sure you have enough empty cells down and/or to the right of the cell where you enter the formula, otherwise a #SPILL error will occur.
• If none of the arguments is specified, a RANDARRAY() formula returns a single decimal number between 0 and 1.
• If the rows or/and columns arguments are represented by decimal numbers, they will be truncated to the whole integer before the decimal point (e.g. 5.9 will be treated as 5).
• If the min or max argument is not defined, RANDARRAY defaults to 0 and 1, respectively.
• Like other random functions, Excel RANDARRAY is volatile, meaning it generates a new list of random values every time the worksheet is calculated. To prevent this from happening, you can replace formulas with values by using Excel's Paste Special > Values feature.

Generate random numbers between two numbers

To create a list of random numbers within a specific range, supply the minimum value in the 3^rd argument and the maximum number in the 4^th argument. Depending on whether you need integers or decimals, set the 5^th argument to TRUE or FALSE, respectively.

As an example, let's populate a range of 6 rows and 4 columns with random integers from 1 to 100. For this, we set up the following arguments of the RANDARRAY function:

• Rows is 6 since we want the results in 6 rows.
• Columns is 4 as we want the results in 4 columns.
• Min is 1, which is the minimum value we wish to have.
• Max is 100, which is the maximum value to be generated.
• Whole_number is TRUE because we need integers.

Putting the arguments together, we get this formula:

=RANDARRAY(6, 4, 1, 100, TRUE)

And it produces the following result:

Generate random date between two dates

Looking for a random date generator in Excel? The RANDARRAY function is an easy solution! All you have to do is input the earlier date (date 1) and later date (date 2) in predefined cells, and then reference those cells in your formula:

For this example, we have created a list of random dates between the dates in D1 and D2 with this formula:

=RANDARRAY(10, 1, D1, D2, TRUE)

Of course, nothing prevents you from supplying the min and max dates directly in the formula if you wish to. Just be sure you enter them in the format that Excel can understand:

=RANDARRAY(10, 1, "1/1/2020", "12/31/2020", TRUE)

To prevent mistakes, you can use the DATE function for entering dates:

=RANDARRAY(10, 1, DATE(2020,1,1), DATE(2020,12,31), TRUE)

Generate random workdays in Excel

To produce random working days, embed the RANDARRAY function in the first argument of WORKDAY like this:

RANDARRAY will create an array of random start dates, to which the WORKDAY function will add 1 workday and ensure that all the returned dates are working days.

With date 1 in D1 and date 2 in D2, here's the formula to produce a list of 10 weekdays:

=WORKDAY(RANDARRAY(10, 1, D1, D2, TRUE), 1)

As with the previous example, please remember to format the spill range as Date to have the results displayed correctly.

How to generate random numbers without duplicates

Though modern Excel offers 6 new dynamic array functions, unfortunately, there is still no inbuilt function to return random numbers without duplicates.

To build your own unique random number generator in Excel, you will need to chain several functions together like shown below.

Random integers:

Random decimals:

Where:

• N is how many values you wish to generate.
• Min is the lowest value.
• Max is the highest value.

For example, to produce 10 random whole numbers with no duplicates, use this formula:

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 100, TRUE)), SEQUENCE(10))

To create a list of 10 unique random decimal numbers, change TRUE to FALSE in the last argument of the RANDARRAY function or simply omit this argument:

=INDEX(UNIQUE(RANDARRAY(20, 1, 1, 100, FALSE)), SEQUENCE(10))

How to randomly sort in Excel

To shuffle data in Excel, use RANDARRAY for the "sort by" array (by_array argument) of the SORTBY function. The ROWS function will count the number of rows in your data set, indicating how many random numbers to generate:

With this approach, you can randomly sort a list in Excel, whether it contains numbers, dates or text entries:

=SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13)))

Also, you can also shuffle rows without mixing your data:

=SORTBY(A2:B10, RANDARRAY(ROWS(A2:B10)))

How to get a random selection in Excel

To extract a random sample from a list, here's a generic formula to use:

Where n is the number of random entries you wish to extract.

For example, to randomly select 3 names from the list in A2:A10, use this formula:

=INDEX(A2:A10, RANDARRAY(3, 1, 1, ROWS(A2:A10), TRUE))

Or input the desired sample size in some cell, say C2, and reference that cell:

=INDEX(A2:A10, RANDARRAY(C2, 1, 1, ROWS(A2:A10), TRUE))

How this formula works:

At the core of this formula is the RANDARRAY function that creates a random array of integers, with the value in C2 defining how many values to generate. The minimal number is hardcoded (1) and the maximum number corresponds to the number of rows in your data set, which is returned by the ROWS function.

The array of random integers goes directly to the row_num argument of the INDEX function, specifying the positions of the items to return. For the sample in the screenshot above, it is:

=INDEX(A2:A10, {8;7;4})

How to select random rows in Excel

If your data set contains more than one column, then specify which columns to include in the sample. For this, supply an array constant for the last argument (column_num) of the INDEX function, like this:

=INDEX(A2:B10, RANDARRAY(D2, 1, 1, ROWS(A2:A10), TRUE), {1,2})

Where A2:B10 is the source data and D2 is the sample size.

As the result, our random selection will contain two columns of data:

How to randomly assign numbers and text in Excel

To do random assignment in Excel, use RANDBETWEEN together with the CHOOSE function in this way:

Where:

• Data is a range of your source data to which you want to assign random values.
• N is the total number of values to assign.
• Value1, value2, value3, etc. are the values to be assigned randomly.

For example, to assign numbers from 1 to 3 to participants in A2:A13, use this formula:

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), 1, 2, 3)

For convenience, you can enter the values to assign in separate cells, say from D2 to D4, and reference those cells in your formula (individually, not as a range):

=CHOOSE(RANDARRAY(ROWS(A2:A13), 1, 1, 3, TRUE), D2, D3, D4)

As the result, you will be able to randomly assign any numbers, letters, text, dates and times with the same formula:

How this formula works

At the heart of this solution is again the RANDARRAY function that produces an array of random integers based on the min and max numbers that you specify (from 1 to 3 in our case). The ROWS function tells RANDARRAY how many random numbers to generate. This array goes to the index_num argument of the CHOOSE function. For example:

=CHOOSE({1;2;1;2;3;2;3;3;1;3;1;2}, D2, D3, D4)

Index_num is the argument that determines the positions of the values to return. And because the positions are random, the values in D2:D4 are picked in a random order. Yep, it's that simple :)

How to randomly assign data to groups

When your task is to randomly assign participants to groups, the above formula may not be suitable because it does not control how many times a given group is chosen. For example, 5 persons could be assigned to group A while only 2 persons to group C. To do random assignment evenly, so that each group has the same number of participants, you need a different solution.

First, you generate a list of random numbers by using this formula:

=RANDARRAY(ROWS(A2:A13))

Where A2:A13 are your source data.

And then, you assign groups (or anything else) by using this generic formula:

Where n is the group size, i.e. the number of times each value should be assigned.

For example, to randomly assign people to the groups listed in E2:E5, so that each group has 3 participants, use this formula:

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

Please notice that it's a regular formula (not a dynamic array formula!), so you need to lock the ranges with absolute references like in the above formula.

Enter your formula in the top cell (C2 in our case) and then drag it down to as many cells as needed. The result will look similar to this:

Please remember that the RANDARRAY function is volatile. To prevent generating new random values every time you change something in the worksheet, replace formulas with their values by using the Paste Special feature.

How this formula works:

The RANDARRAY formula in the helper column is very simple and hardly requires explanation, so let us focus on the formula in column C.

=INDEX($E$2:$E$5, ROUNDUP(RANK(B2,$B$2:$B$13)/3,0))

The RANK function ranks the value in B2 against the array of random numbers in B2:B13. The result is a number between 1 and the total number of participants (12 in our case).

The rank is divided by the group size, (3 in our example), and the ROUNDUP function rounds it up to the nearest integer. The result of this operation is a number between 1 and the total number of groups (4 in this example).

The integer goes to the row_num argument of the INDEX function, forcing it to return a value from the corresponding row in the range E2:E5, which represents the assigned group.

#SPILL error

As with any other dynamic array function, a #SPILL! error most often means that there isn't enough space in the intended spill range to display all the results. Just clear all the cells in this range, and your formula will recalculate automatically. For more information, please see Excel #SPILL error - causes and fixes.

#VALUE error

A #VALUE! error may occur in these circumstances:

• If a max value is less than a min value.
• If any of the arguments is non-numeric.

#NAME error

In most cases, a #NAME! error indicates one of the following:

• The function's name is misspelled.
• The function is not available in your Excel version.

#CALC! error

A #CALC! error occurs if the rows or columns argument is less than 1 or refers to a blank cell.