0

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
1

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.

1

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.

0

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 at 4:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.