The RANDARRAY function is a powerful new function that makes RAND and RANDBETWEEN obsolete. When used without any arguments, RANDARRAY works just like the RAND function and returns a single random number between 0 and 1, but when specifying the optional arguments you can generate arrays of random numbers that can be integer or decimal values and with any required minimum and maximum values.
The RANDARRAY function is a volatile function which automatically recalculates and generates a new random array of results in each calculation cycle. This function can be used to generate random sample data for Monte Carlo style statistical and stochastic analysis.
You can find the examples in this blog for RANDARRAY in this Excel workbook:.
RANDARRAY ([rows], [columns], [min],[max], [integer]) | |
rows | Number of rows of random numbers to generate (default is 1). |
columns | Number of columns of random numbers to generate (default is 1). |
min | Minimum of values to generate (default is 0). |
max | Maximum of values to generate (default is 1). |
integer | TRUE to return integer values (default is FALSE). |
NOTE: This function is volatile, so it recalculates new random numbers each time the worksheet changes. |
This example generates a 10 row by 15 column array of random integer values between 100 and 500:
Figure 1 Example 1
The actual formula used in the above example is rather complicated in order to simulate how the RANDARRAY function works when used in the typical manor. Generally RANDARRAY will specify hard-coded arguments to fill a range with the required random array of values to meet the requirements of the task as hand.
Since all arguments of RANDARRAY are optional, this formula shows how the RANDARRAY function would operate if arguments are omitted by supplying the correct default values when the input cell for the argument is empty:
=RANDARRAY(IF(ISBLANK(B13),1,B13),IF(ISBLANK(B14),1,B14),IF(ISBLANK(B15),0,B15),IF(ISBLANK(B16),1,B16),IF(ISBLANK(B17),FALSE,B17))
You can change or remove arguments in the worksheet to try out different combinations and see how RANDARRAY operates to generate new random data for each change.
The next example uses SORTBY with RANDARRAY to sort a list in a random order:
Figure 2 Example 2
The formula to sort the names in the table SampleList uses RANDARRAY to create a random list of numbers to use with SORTBY to sort the names:
=SORTBY(SampleList,RANDARRAY(10))
This last example generates anagrams for a phrase using RANDARRAY with LEN, MID, SEQUENCE, SORTBY, PROPER, and CONCAT:
Figure 3 Example 3
The input phrase for which the anagram will be generated is in cell B4, and the final anagram result is in cell E4. There are several intermediate steps to calculating the anagram:
This does not need to be implemented with separate formulas and using cells for intermediate calculations – it was done that way for clarity, to make it easier to see how the calculations work. The same calculation can be done using a single formula using the LET function:
=LET(_len,LEN(B4),chars,MID(B4,SEQUENCE(_len),1),rnd_order,SORTBY(SEQUENCE(_len),RANDARRAY(_len)),new_chars,SORTBY(chars, rnd_order),PROPER(CONCAT(new_chars)))
The RANDARRAY function is supported in these GrapeCity spreadsheet controls and components: