Stable formula to randomly shuffle the rows of an Excel range. Does not change output on recalculation.
Syntax: =\SHUFFLE.ROWS.STABLE(seed, range) => array
=LAMBDA(seed,range,
LET(
rw, ROWS(range),
s, INT(IF(AND(seed>0,seed<1),seed*16777213,seed)),
TAKE(
SORT(
HSTACK(
SCAN(s,SEQUENCE(rw,1,0,0),
LAMBDA(acc,val,MOD(acc*6423135,16777213))
),
range
),
1
),
rw,
-COLUMNS(range)
)
)
)
Documentation
Returns the source range or array with rows in pseudorandom order.
Seed is expected to be a large integer or a fraction between 0 and 1.
This is a stable version of the \SHUFFLE.ROWS function.
Commentary
Please see other SHUFFLE and .STABLE functions for the explanation of the pseudorandom number generator algorithm used in this function.