setting seed for excel random number

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.


You could use a VBA UDF() based on the Rnd() function. See:

Repeating random variables in VBA


I am not pretending that it is a perfect solution, but that works for me. The beauty of it, is that I can assign a random number to a particular cell:

Public Function GetRandom(seed As Double, min As Double, max As Double) As Double
    Dim colrow As Double
    Dim range As Double

    range = max - min
    If (Application.Caller.Column() = Application.Caller.Row()) Then
        colrow = (Log(Application.Caller.Column() + 1) * Log(Application.Caller.Row() + 1)) * seed
    Else
        colrow = (Log(Application.Caller.Column() + 1) / Log(Application.Caller.Row() + 1)) * seed
    End If
    Rnd (-1)
    Randomize colrow 
    test = Rnd * range - range / 2
    GetRandom = colrow 
End Function

Usage:

=GetRandom($Z$1,1,-1)

I my example, the seed value is in Z1 cell, but of course in can be in any other cell. It also allow me to setup min and max values.

enter image description here