The RANDARRAY function in Google Sheets is the array form of the popular RAND function in it. Both are volatile functions categorized under MATH in Google Sheets.
While the RAND function generates a simple random number between 0 and 1, the RANDARRAY function generates an array result.
In Excel, I could see that this function retains the functionality of both RAND and RANDBETWEEN. But in Google Sheets, it doesn’t. I’ll explain that later.
How to Use the RANDARRAY Function in Google Sheets
The function RANDARRAY generates an array (‘n’ rows x ‘n’ columns) of random numbers between 0 and 1.
Syntax:
RANDARRAY(rows, columns)
rows – number of rows tall.
columns – number of columns wide.
Example:
=randarray(5,3)
This formula will generate random numbers between 0 and 1 which is 5 rows taller and 3 columns wider.
Random Numbers 10 Rows x 1 Column:
RANDARRAY Formula in Real Life Use in Google Sheets
You can make use of this function in several real-life scenarios. See two such examples.
Randomize a Data Range in Google Spreadsheets
The range A2:B contains the name and age of few peoples. Let me show you how to sort this data range randomly.
={"Name","Age";SORT(A2:B11,randarray(10,1),TRUE)}
To understand this SORT and RANDARRAY combination, please see the below SORT function syntax.
SORT(range, sort_column, is_ascending)
You can see that, in my above example, I have replaced the sort_column with the RANDARRAY formula.
Extract ‘n’ Random Rows from a Data Range
To extract ‘n’ random rows you can use the above formula with some addition. I mean just use ARRAY_CONSTRAIN with the formula above.
=array_constrain({"Name","Age";SORT(A2:B11,randarray(10,1),TRUE)},6,2)
This formula returns 5 random rows and 1 additional row that contain the field labels.
Difference Between RANDARRAY, RAND, and RANDBETWEEN in Docs Sheets
RAND – generates a random number between 0 to 1.
RANDARRAY – It’s the array form of the above. That means to generate an array of numbers between 0 to 1.
RANDBETWEEN – It returns a random integer between two given values.
The RANDBETWEEN function can also generate array values using ROW function and ARRAYFORMULA with it.
Example:
=ArrayFormula(randbetween(1,row(A1:A5)))
Must Check: Google Sheets Functions Guide.
In Excel, similar to RANDBETWEEN, you can include the low and high values in RANDARRAY formula.
Example: 10 rows and 1 column between the number 1 to 100 (formula will only works in Excel).
=randarray(10,1,1,100)
Related Reading:
- Frequency of Recalculation of Google Sheets NOW, TODAY, RAND, RANDBETWEEN Functions.
- How to Pick a Random Name from a Long List in Google Sheets.
- Macro-Based Random Name Picker.
- How to Randomly Select N Numbers from a Column in Google Sheets.
- How to Randomly Extract a Certain Percentage of the Rows in Google Sheets.