Stable, non-volatile version of the Excel RANDARRAY() function. Return the same array of random numbers on each recalculation.
Syntax: =\RANDARRAY.STABLE(seed,[rows],[columns],[min],[max],[integer]) => array of numbers (min<=n<=max)
=LAMBDA(seed,[rows],[columns],[min],[max],[integer],
LET(
s, IF(AND(seed<1,seed>0),INT(seed*16777213),INT(seed)),
min, IF(ISOMITTED(min),0,min),
max, IF(ISOMITTED(max),1,max),
rows, IF(ISOMITTED(rows),1,rows),
columns, IF(ISOMITTED(columns),1,columns),
tb, max-min+IF(integer,1,0),
randarr,
SCAN(s,SEQUENCE(rows,columns,0,0),
LAMBDA(acc,val,
MOD(acc*6423135,16777213)
)
),
MAP(randarr,
LAMBDA(x,
MOD(x,tb)+min+IF(integer,0,x/16777213)
)
)
)
)
Documentation
Returns an array of pseudorandom numbers between min and max using a simple linear congruential generator.
The seed to the function should be a large integer or a fraction between 0 and 1. It is advisable to use the output of TODAY() or similarly random date.
Returned array will be [rows] rows tall and [columns] columns wide. Omitted arguments default to 1.
Omitted [min] defaults to 0, omitted [max] defaults to 1.
If [integer] is TRUE, returned values will be integers. Default: FALSE.
Commentary
This function was designed to resemble Excel’s RANDARRAY() as much as possible. There is a tradeoff there. The random functions could be more consistent with each other, i.e. have matching argument names and order, or they could be consistent with the volatile, built-in Excel versions. I went with the second option.