The RANDARRAY function combines two previous Excel functions, RAND and RANDBETWEEN, into a new, dynamic formula. RANDARRAY allows you to create a set of randomized numbers defined by specific user parameters, allowing great flexibility. There are a few different inputs to go over that will effect the type of random array being generated.
Syntax
= RANDARRAY([rows], [columns], [min], [max], [integer])rows = number of rows to create in the random array
columns = number of columns to create in the random array
min = minimum (floor) number able to be generated by the random array
max = maximum (ceiling) number able to be generated by the random array
integer = a TRUE/FALSE value. This parameter set to TRUE will only return whole numbers (no decimals). FALSE will return fractional numbers (decimals). Default value is FALSE
Explanation
This function is part of the "Math and Trigonometry" function group. It's primary purpose is generate one or many random numbers with some specific set parameters. It can also be used in conjunction with other formulas to pick a randomized value from a set list.
RANDARRAY is a volatile function, which means that the value generated will change every time any cell in the workbook is changed.
The default minimum is 0 and the default maximum is 1. If you do not enter in a minimum or maximum value, the RANDARRAY function will generate a decimal between 0 and 1.
If the minimum is not smaller than the maximum, an #VALUE error will be thrown.
Examples
How to Generate One Random Number
By using the a blank RANDARRAY formula and all of its default settings, you get a single random value between 0 and 1.
= RANDARRAY()
How to Generate A Column of Random Numbers
Using the RANDARRAY formula, and adding in a "5" in the row argument section, you get a five-row array of random numbers between 0 and 1.
= RANDARRAY(5)
How to Generate A Row of Random Numbers
Just as with the row example, using a 3 in the column argument of the formula, will give you 3 random numbers across 3 columns.
= RANDARRAY(,3)
How to Generate an Array of Numbers Between Two Values
Combining both the row and column arguments, the formula will return an array with the specified amounts of rows and columns. In the below example, the minimum number is set to 1, the maximum number is set to 100, and the formula will only return whole numbers, no decimal numbers.
= RANDARRAY(5,3,1,100,TRUE)
How to Randomize a List of Values
To take a list of values, in this case names, and randomize them, we need to combine a few different functions.
= SORTBY(D3:D9, RANDARRAY(COUNTA(D3:D9)))Here, we are creating a random array using RANDARRAY, and as we are using a list of names, COUNTA is used to count how many values are in our list, and passing that to the RANDARRAY to use as the row count. This gives us a hidden array of random numbers, the same length as our list of values.
= RANDARRAY(COUNTA(D3:D9))Gives us:
We then need to use the SORTBY function, to sort our list of names by the randomized value, giving us a randomized ranking of each name.
How to Randomly Pick a value from a List, excluding a Specific Value
The idea behind this example is similar to the above example. Except here, we are taking a list of values, and picking one at random, excluding a specific value that we do not wish to return. There are a few more pieces that we have to be aware of and account for.
=@SORTBY(FILTER(D3:D9, D3:D9 <> B6), RANDARRAY(COUNTA(D3:D9)-COUNTIF(D3:D9, B6)))
To start with RANDARRAY, we are taking the same count of the values in the list, but we also need to subtract out any of the values that we are looking to exclude. That can be accomplished using a COUNTIF, and counting the number of times the value occurs. That gives us the correct number of rows that will be passed to the SORTBY.
The SORTYBY is also using a filtered range as its array. We are filtering the list of values and keeping everything except (<>) the value we are looking to exclude.
The @ symbol in the front of the formula takes the dynamic spilled array that would normally be returned of the list excluding the value, and only returns the 1st cell.