0

I am running a experiment in excel using NORM.S.INV(RAND())

Is there a way to set the seed for the random function?

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Santiagopph48
  • 131
  • 1
  • 2
  • 8
  • 2
    Does this answer your question? [setting seed for excel random number](https://stackoverflow.com/questions/33161114/setting-seed-for-excel-random-number) – jsheeran Jun 09 '21 at 10:07

1 Answers1

2

I found I needed persisted random numbers all the time. Turns out this is possible using only standard spreadsheet formulas.

One way is to use the so called Lehmer random number method. It generates a sequence of random numbers in your spreadsheet that stays the same until you change the "seed number", a number you choose yourself and will recreate a different random sequence for each seed number you choose.

The short version:

  1. In cell B1, enter your "seed" number, it can be any number from 1 to 2,147,483,647
  2. In cell B2 enter the formula =MOD(48271*B1,2^31-1) , this will generate the first random number of your sequence.
  3. Now copy this cell down as far as the the random sequence you want to generate.

That's it! If you want a different set of numbers, just change the seed number in B1. If you ever want to recreate the same set of numbers again, just use the same seed and the same random sequence will appear.

More details in this tutorial: How to generate random numbers that don't change in Excel and Google Sheets

Hjalmar Gislason
  • 31
  • 3
  • Please don't link to external sites without putting the salient details in the answer itself. External links have a habit of breaking over time. – Enigmativity May 04 '22 at 22:51
  • Thanks Enigmativity and @MarcoBonelli , I'm new here. Appreciate the assistance to start off on the right foot. I updated the reply to include the core of the solution rather than just the link. Hope this is better aligned with what you're looking for. – Hjalmar Gislason May 07 '22 at 18:13
  • This random number generator seems suspiciously not random, it has continuously increasing values if you put in 1-10-100-1000-10000 and so on? – John Doe Sep 24 '22 at 10:03