randomexcel-2010random-seed

# Setting seed for Excel random number generator

In excel below formula will generate random number from a normal distribution with mean 10 and variance 1. Is there a way to set a fix seed so that i get a fix set of random numbers all the time? I am using Excel 2010

``````=NORMINV(RAND(),10,1)
``````

Solution

• You can implement your own random number generator using spreadsheet functions. For example, C++11 has a Lehmer random number generator called `minstd_rand` which is obtained by the recurrence

``````X = X*g (mod m)
``````

where `g = 48271` and `m = 2^31-1`

In `A1` you can place your seed value. In `A2` enter the formula:

``````=MOD(48271*A1,2^31-1)
``````

and copy it down however far you need.

In `B2` enter `=A2/(2^31-1)` and in `C2` enter `=NORM.INV(B2,10,1)`, copying as needed. Note that you can always replace the seed value in `A1` by

``````=RANDBETWEEN(1,2^31-2)
``````

if you want to turn volatile randomness back on.

The following screenshot shows 25 random normal variables generated in this fashion:

As you can tell from the histogram the distribution seems roughly normal.