26

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)
Marco Bonelli
  • 63,369
  • 21
  • 118
  • 128
user2543622
  • 5,760
  • 25
  • 91
  • 159
  • there are references that suggest there is no such method. http://answers.microsoft.com/en-us/office/forum/office_2003-excel/what-define-the-random-seed-within-excel/49085e19-3a63-4191-92db-27da5428b435 – EngrStudent Oct 16 '15 at 01:31
  • Are you simply trying to get a nonvolatile set of random numbers, or do you really need to control the seed? – John Coleman Oct 16 '15 at 10:49

5 Answers5

43

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.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • So, which value should I use as the random number? The numbers in the B column, I presume? – Vylix Sep 15 '20 at 09:06
  • That's correct: if you want a uniform random distribution, choose column B. – whiskeychief Sep 29 '20 at 14:00
  • Is there a condition on the choice of seed value? E.g., if I choose a seed between 1 and 1000, the first generated number is far below `m`. So, the random sequences starting with those seeds all start with a 'low' random value. Is there a way to ensure that, for any choice of consecutive seeds, the first generated value from each is *uniformly* distributed in the interval from 1 to m-2? – ElRudi Oct 21 '22 at 10:33
  • @ElRudi The seed can be any number in the range. If you start with a number less than 1000 then you start with a number less than 1000. But if you start with e.g. 24012 one could object that the starting number is unusually close to 24000. Same basic logic. There is nothing really special about a number being less than 1000. If it becomes an issue, you could always throw away the first couple outputs after the seed. – John Coleman Oct 21 '22 at 11:05
  • Impressive response time there on a 2-year-old question :) I would expect the first number *after the seed* already be uniformly distributed - regardless of what's picked as the seed. Because I would expect no correlation between the input and the output value. But I now understand it takes a few iterations for the numbers in a sequence to get "out of step" with the numbers in another, if the seed values of both are small. I guess the best way is to take evenly spaced seeds in the interval from 1 to m-1 – ElRudi Oct 21 '22 at 13:07
4

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

Repeating random variables in VBA

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
3

A pragmatic solution is to copy values from your sample into a new range.

Leo
  • 2,775
  • 27
  • 29
2

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

koryakinp
  • 3,989
  • 6
  • 26
  • 56
0

I've implemented xorshift32 for Excel (also works on Google Sheets). Replace "input" to "A1" or something.

=LET(LX, LAMBDA(X, A, BITXOR(X, BITLSHIFT(BITAND(X, 2^(32-A)-1), A))), RX, LAMBDA(X, A, BITXOR(X, BITRSHIFT(X, A))), LX(RX(LX(input, 13), 17), 5))
kuboon
  • 9,557
  • 3
  • 42
  • 32