setting seed for excel random number

random excel-2010 random-seed
user2543622 picture user2543622 · Oct 16, 2015 · Viewed 34.2k times · Source

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)

Answer

John Coleman picture John Coleman · Oct 16, 2015

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.

Related questions

How do I generate random integers within a specific range in Java?

How do I generate a random int value in a specific range? I have tried the following, but those do not work: Attempt 1: randomNum = minimum + (int)(Math.random() * maximum); // Bug: `randomNum` can be bigger than `maximum`. Attempt 2: Random rn = new …

Read More
How do I generate a random int number?

How do I generate a random integer in C#?

Read More
Getting random numbers in Java

I would like to get a random value between 1 to 50 in Java. How may I do that with the help of Math.random();? How do I bound the values that Math.random() returns?

Read More