Search code examples
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:

    enter image description here

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