Stable, non-volatile version of the Excel RAND() function. Return the same random number on each recalculation.
Syntax: =\RAND.STABLE(seed) => number (0<n<1)
=LAMBDA(seed,
MOD(
INT(
IF(AND(seed>0,seed<1),seed*16777213,seed)
)*6423135,
16777213
)/16777213
)
Documentation
Returns a pseudorandom fraction between 0 and 1 using a simple linear congruential generator.
For best results, seed for a subsequent call to the \RAND.STABLE function should be the output of the previous call to the function.
The seed to the first call to the function should be a large integer. It is advisable to use the output of TODAY() or similarly random date.
Blog
This function is not very innovative, but it’s been desperately missing from Excel, probably for a reason.
The numbers for a (6423135) and m (16777213) haven’t been chosen at random. They come from this paper. The size has been selected so as to ensure that with Excel’s 15 digit precision, no rounding occurs after the multiplication, which would affect the randomness in an unpredictable way and only obfuscate the inherent shortcomings of this function.
Safe to say, this should not be used to cryptographically secure anything or select winners of any high-stakes raffles. Fantasy football might be OK.