JavaScript and .NET Spreadsheet Tips and Tricks: RANDARRAY Functions

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 Syntax

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:

1. First, the length of the phrase is calculated using the LEN function in cell C4 using the formula =LEN(B4).
2. Next, each letter in the phrase is separated into a separate cell to the spilled range B5# using MID with SEQUENCE to extract each character into a different row using the formula =MID(B4, SEQUENCE(C4),1).
3. Next, a new ordering for the characters is generated in the spilled range C5# using SORTBY with SEQUENCE and RANDARRAY using the formula =SORTBY(SEQUENCE(C4),RANDARRAY(C4)).
4. Next, each letter in the phrase is sorted into the new random ordering in the spilled range E5# with SORTBY using the formula =SORTBY(B5#, C5#).
5. Finally, the anagram is calculated in cell E4 using PROPER with CONCAT using the formula =PROPER(CONCAT(E5#)).

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)))

RANDARRAY in GrapeCity Products

The RANDARRAY function is supported in these GrapeCity spreadsheet controls and components:

• Spread.NET Windows Forms Spreadsheet Control | Demo
• SpreadJS Javascript/Typescript/Angular/React/Vue Spreadsheet Widget | Demo
• GrapeCity Documents for Excel .NET and Java controls | .NET Demo | Java Demo