RANDARRAY() Function

The RANDARRAY() function is one of the new Dynamic Array functions released by Microsoft and it enhances the RANDBETWEEN() function.

What RANDARRAY() does is not only to allow you to generate a random number between a smaller and a larger number, but it also enables you to set a range of cells to expand into which means that we can now generate one number in one cell or two in two cells and one column … random numbers in x rows and y columns.

Yesterday, I posted an answer here on the GESTEP() function in which I used but did not discuss the RANDARRAY() function:

I used RANDARRAY() in the range B7:B14, using this formula: =RANDARRAY(8,1,140,185,TRUE).

The syntax of RANDARRAY() is =RANDARRAY([rows],[columns],[min],[max],[integer])

My formula, then, tells us that I wanted to create a range or array of random numbers

• 8 rows deep
• 1 column wide
• Minimum value 140
• Maximum value 185 and
• I wanted my random numbers to be integers, TRUE, as opposed to decimals

Notice that all of the variables in the function are optional, they are all in square brackets. This means that if we enter =RANDARRAY(), we will still get a valid result, something like this: 0.214643 …

If you enter the formula I used in GESTEP for yourself, you will get something like this:

As a matter of interest, change the formula to this: =RANDARRAY(5,3,140,185,TRUE) and your range of random numbers will change to this:

Change the formula to =RANDARRAY(5,3,140,185,FALSE) to see this:

Change the formula to this now: =RANDARRAY(15,2,,,FALSE) and you will see this:

Make another change, =RANDARRAY(,3,140,185,TRUE) to get this:

That is, omit the rows value or set it to one and we get a single row.

If you want to sort your random numbers, try this:

Enter this formula instead of the previous one to see what you get: =SORT(RANDARRAY(13,,140,185,TRUE),,-1)

These are random numbers, don’t forget, so your lists will contain different numbers to mine.

How about the following? Enter this new version of the function that I have nested inside the TEXT() function …

Excel has assumed that your numbers 1 to 7 relate to days of the week, which is what I intended.

And this?

="The number of reject products is "&RANDARRAY(5,1,1,7,TRUE)

And this?

=RANDARRAY(5,2,10,70,TRUE)&" people reported a crime this morning and "&RANDARRAY(5,2,5,25,TRUE)&" animals died "

Note, that creates, effectively, two similar lists but do see that the numbers change from list one to list two.

We only enter this formula once, in cell E1, for example but it will SPILL down 5 rows or 15 rows or X and 1, 2, X columns according to what we have programmed. BUT, if there is something already in the range you want to fill or spill into, this happens:

In this case, I had already entered xxx inside the spill range so since Excel could not complete the task, it stops and says #SPILL!. Just delete xxx or put your formula somewhere else and it will work again.

Finally, something a bit more adventurous:

Download my scratchpad file ...

Duncan Williamson

27th April 2020 & 11th January 2021