RANDARRAY Function Examples – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 9, 2023

This tutorial demonstrates how to use the RANDARRAY Function in Excel and Google Sheets to generate an array of random numbers.

RANDARRAY Formula Main

RANDARRAY Function Overview

The RANDARRAY Function is used to return an array of random numbers. It was introduced as a replacement for the RAND Function and the RANDBETWEEN Function.

The RANDARRAY Function simplifies the process of generating random numbers by:

  1. Allowing the user to input the formula in only one cell.
  2. Allowing the user to specify the number of random values to be generated.

How to use the RANDARRAY Function

Return Multiple Rows and Columns

We can change the size of the array that the RANDARRAY Function returns by changing the number of rows and columns.  To do this we specify the [rows] and [columns] arguments.

To return an array of random numbers that is 5 Rows tall and 1 Column wide, we enter the following formula in cell A2:

=RANDARRAY(5, 1, 1, 10, TRUE)

RANDARRAY EX 04

To return an array of random numbers that is 5 Rows tall and 3 Columns wide, we enter the following formula in A2:

=RANDARRAY(5, 3, 1, 10, TRUE)

RANDARRAY EX 06

Return Numbers Between Two Values

When we want to return numbers between two values, we specify the values in the [min] and [max] arguments.

To return a list of random numbers between 1 and 10, we enter the following formula in A2:

=RANDARRAY(5, 1, 1, 10)

RANDARRAY EX 13

Return Decimal Numbers or Whole Numbers

To return 5 decimal numbers between 1 and 10, we enter the following formula in A2:

=RANDARRAY(5, 1, 1, 10, FALSE)

RANDARRAY FALSE

Note that we’ve set the [integer] argument to FALSE which means the output will be decimal numbers.

The formula can also be written as:

=RANDARRAY(5, 1, 1, 10)

When the [integer] argument is not specified it defaults to FALSE:

RANDARRAY Blank

 

To return 5 whole numbers between 1 and 10, we enter the following formula in A2:

=RANDARRAY(5, 1, 1, 10, TRUE)

RANDARRAY EX 04

Note that we’ve set the [integer] argument to TRUE which means the output will be whole numbers,

RANDARRAY Errors

#VALUE!

This error occurs when the value in the [min] argument is greater than the value in the [max] argument.

RANDARRAY EX 08

To correct this error, set the value in the  [min] argument to a value that is less than that in the [max] argument.

RANDARRAY EX 09

#SPILL!

This error occurs when there is a value in the Spill Range i.e. the range where the RANDARRAY Function places its results.

To correct this error, clear the range that Excel highlights.

RANDARRAY EX 10

RANDARRAY Tips & Tricks

  1. Since all the arguments in the RANDARRAY Function are optional, entering the formula below in any cell returns a single value between 0 and 1:

    =RANDARRAY()

     

    RANDARRAY EX 11

  2. If the [min] and [max] arguments are not specified, RANDARRAY will default to 0 and 1 respectively and return an array of random numbers between 0 and 1.
    =RANDARRAY(5, 1, , , FALSE)

     

    RANDARRAY EX 12

  3. If the [integer] argument is not specified, RANDARRY will default to FALSE and return an array of decimal numbers:
    =RANDARRAY(5, 1, 1, 10 )

    RANDARRAY EX 13

  4. RANDARRAY is different from the RAND and RANDBETWEEN functions. While RAND and RANDBETWEEN return a single value, RANDARRAY returns an array.
  5. Ensure that the cells below the input cell are blank to avoid the Spill Error.

 

RANDARRAY in Google Sheets

The RANDARRAY Function works exactly the same in Google Sheets as in Excel:

RANDARRAY Google Function

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions