How to Use the RANDARRAY Function in Google Sheets

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.

RANDARRAY 5 rows x 3 columns

Random Numbers 10 Rows x 1 Column:

RANDARRAY 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)}
Real life example to RANDARRAY function in Google Sheets

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)
Extracting 'n' random rows using the RANDARRAY function

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:

  1. Frequency of Recalculation of Google Sheets NOW, TODAY, RAND, RANDBETWEEN Functions.
  2. How to Pick a Random Name from a Long List in Google Sheets.
  3. Macro-Based Random Name Picker.
  4. How to Randomly Select N Numbers from a Column in Google Sheets.
  5. How to Randomly Extract a Certain Percentage of the Rows in Google Sheets.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.