Stable, non-volatile version of the Excel RANDBETWEEN() function. Return the same random integer on each recalculation.
Syntax: =\RANDBETWEEN.STABLE(bottom, top, seed, [index]) => number (bottom<=n<=top)
=LAMBDA(bottom,top,seed,[index],
LET(
i,IF(NOT(index),COLUMN()+(ROW()-1)*COLUMNS($1:$1),index),
tb,top-bottom+1,
rnd,MOD(
MOD(
INT(IF(AND(seed<1,seed>0),seed*16777213+i,seed+i))*6423135,
16777213
),
tb
),
rnd+bottom
)
)
Documentation
Returns a pseudorandom fraction between bottom and top 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.
[Index] is expected to be the index of the record for which the formula is calculated. If [index] is not supplied, the seed will be augmented by the position of the cell containing the function within a worksheet.
[Index] essentially works as a second seed, and can be supplied to make the output of the function stable when records are sorted or columns shifted.
Blog
This function is a little more involved than RAND.STABLE(). With the output being an integer (usually small and from an even smaller range), it cannot serve as a seed for the next call, so I had to get creative with the i parameter.
I believe this function is no more questionable than the inclusion of Excel’s original RANDBETWEEN().