I understand the use of the function rand means the output is updated with every modification to the sheet contents. I am looking for a way to fix a seed so that random numbers don't change unless I change the seed.
I am aware of Google Sheets extensions that solve the problem, but would like a pure formula solution.
In cell A1, put your integer seed value - this can be anything, but a small number is fine (something like "34"). In cell A2, paste the following formula which auto-seeds the first cell of the pseudorandom sequence:
=MOD(ROUND(2147483647*16807*MOD(ROUND(MOD(A1*EXP(1),1)*2147483647*16807,0),2147483647)/2147483647,0),2147483647)/2147483647
In cell A3, paste this to generate the next value in the sequence:
=MOD(ROUND(2147483647*16807*A2,0),2147483647)/2147483647
... And then fill to the right to generate as many additional numbers as you wish. The cycle period is 2B.
Google Sheets doesn't has a "pure formula" that generates a sequence of random numbers that are fixed until "the seed" is changed.
The above because RAND is a volatile built-in function an it's recalculated according to the spreadsheet recalculation settings.
It's worth to note that a custom function (requires Google Apps Script) is recalculated when its parameters change.
This should work for you: https://www.ssl.berkeley.edu/~mlampton/RandomSpreadsheet4.pdf
It only takes a few minutes to set up
I found I needed persisted random numbers all the time. Turns out this is possible using only standard spreadsheet formulas. Wrote the method up in this tutorial (it's similar to the one suggested by Ken above, but with more details and copyable samples):
Generate random numbers that don't change in Excel and Google Sheets
