# How can I seed a sequence of random numbers in Google Sheets?

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.

• Sure, but that means I have to save the whole sequence of numbers to repeat each simulation, instead of just one number. Oct 19, 2017 at 16:13
• A pure formula solution would be to implement your own pseudorandom generator in Google Sheets. How hard it will be depends on how much entropy you need: the type of numbers and the quantity.
– user135384
Oct 20, 2017 at 18:13

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

• Welcome. While the link could be helpful, on this site we expect that answers include a brief summary of the linked content as it could be removed and then the answer becomes invalid.
– Rubén
May 18, 2019 at 4:50

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