# 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. – twalbaum Oct 19 '17 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 '17 at 18:13

In cell A2, put your integer seed value - this can be anything, but a small number is fine (something like "34"). In cell A1, paste the following formula which auto-seeds the first cell of the pseudorandom sequence:

``````=MOD(ROUND(2147483647*16807*MOD(ROUND(MOD(A2*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 '19 at 4:50