RANDARRAY function in Excel

RANDARRAY Function

The RANDARRAY function makes use of the changes made to Excel’s calculation engine.  These changes enable a single formula to spill results into multiple cells.  The regular RAND function calculates a single random number, which is greater than or equal to zero and less than 1.  The RANDARRAY function calculates a random number in the same way, but the user can specify the number of rows, columns, minimum, maximum and rounding of the random numbers returned.

At the time of writing, the RANDARRAY function is only available to those on a Microsoft 365 subscription.  It will not be available in Excel 2019 or earlier versions.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0038 RANDARRAY function in Excel.xlsx 

Arguments of the RANDARRAY function

RANDARRAY has five arguments, all of which are optional.

=RANDARRAY([Rows], [Columns], [Min], [Max], [Integer])
  • [Rows]: The number of rows of random numbers to return.  If this argument is excluded, the default is 1.
  • [Columns]: The number of columns of random numbers to return.  If this argument is excluded, the default is 1.
  • [Min]: The lowest number to be returned.  If excluded, the default is 0.
  • [Max]: The highest value to be returned. If excluded, the default value is 0.999999999999999 (i.e., just less than 1)
  • [Integer]: TRUE = return whole numbers only, FALSE = return decimal values to 15 decimal places.

As all the arguments are optional, we can use RANDARRAY() without any arguments to create a single random number.

One attribute to be aware of is that RANDARRAY is a volatile function. This means that it recalculates every time any cell changes.  If you’ve used the RAND function before, this functionality will not be new to you.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Examples of using the RANDARRAY function

The following examples illustrate how to use the RANDARRAY function.

Example 1 – Basic usage

In the screenshots below, we see basic examples of using the RANDARRAY function.

Single dimension

RANDARRAY with rows

The formula in cell B2 is:

=RANDARRAY(4)

The formula above creates an array of random numbers which is four rows and one column in size.

Multiple dimensions

RANDARRAY with rows and columns

The formula in cell D2 is:

=RANDARRAY(4,3)

The formula above creates an array of random numbers which is four rows and three columns in size.

Example 2 – Using RANDARRAY with SORTBY

This example shows how to turn a sorted list into a random order.

RANDARRAY to sort list of names

The formula in cell D3 is:

=SORTBY(B3:B10,RANDARRAY(COUNTA(B3:B10)))

RANDARRAY(COUNTA(B3:B10)) creates a list of random numbers which is the same size as the source data.  SORTBY is then used to put cells B3-B10 into the order of the random numbers.

Example 3 – Using all the arguments of RANDARRAY

In the screenshot below, we can see all the arguments of the RANDARRAY functions.

RANDARRAY with all arguments

The formula in cell B2 is:

=RANDARRAY(4,3,50,150,TRUE)

The formula creates an array of results which is four rows, three columns, a minimum of 50, a maximum of 150, with only whole numbers.

Example 4 – Replacing RAND and RANDBETWEEN

There are two existing functions in Excel which already create random numbers: RAND and RANDBETWEEN.  We no longer need these functions, as RANDARRAY can replace both of these.

Replacing RAND

RANDARRAY without arguments

The formula in cell B2 is:

=RANDARRAY()

The default options applied by the RANDARRAY function provide the same result as the RAND function.

Replacing RANDBETWEEN

The RANDBETWEEN function selects integers between an upper and lower limit.

RANDARRAY simulating RANDBETWEEN

The formula in cell D2 is:

=RANDARRAY(,,10,20,TRUE)

This demonstrates that RANDARRAY has the ability to select a minimum, maximum and return integers.  Therefore, RANDARRY provides the same result as RANDBETWEEN.

Want to learn more?

There is a lot to learn about dynamic arrays and the new functions.  Check out my other posts here to learn more:

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *