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)
-
there are references that suggest there is no such method. answers.microsoft.com/en-us/office/forum/office_2003-excel/…EngrStudent– EngrStudent2015-10-16 01:31:12 +00:00Commented Oct 16, 2015 at 1:31
-
Are you simply trying to get a nonvolatile set of random numbers, or do you really need to control the seed?John Coleman– John Coleman2015-10-16 10:49:22 +00:00Commented Oct 16, 2015 at 10:49
Add a comment
|
5 Answers
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.
5 Comments
Vylix
So, which value should I use as the random number? The numbers in the B column, I presume?
whiskeychief
That's correct: if you want a uniform random distribution, choose column B.
ElRudi
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?
John Coleman
@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.
ElRudi
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
You could use a VBA UDF() based on the Rnd() function. See:
Comments
A pragmatic solution is to copy values from your sample into a new range.
A not-so-pragmatic solution in Excel 365 is to create the following named lambda, which takes an array size and an optional seed, and implements the minstd_rand method from another answer. Notice I'm throwing away the first 5 elements to avoid biases with seeds that are small in magnitude:
Name: minstd_rand_01
Formula:
=LAMBDA(n,[seed], LET(
_0, "https://en.wikipedia.org/wiki/Lehmer_random_number_generator",
period, 2 ^ 31 - 1,
_seed, IF(ISOMITTED(seed), RANDBETWEEN(1, period), seed),
_1, "Discard first 5 elements to avoid bias with seeds smaller than 48271",
ra, SCAN(_seed, SEQUENCE(n + 5), LAMBDA(prev,_, MOD(48271 * prev, period))),
INDEX(ra, SEQUENCE(n,,5)) / (period)
))
For example, to produce 5 variates using seed 3, call the lambda using =minstd_rand_01(5,3).
3 Comments
hanjo
I understand this formula up until the last line where it says "(5,3)". When I punch the entire Lambda formula into Excel, I'm also getting "#REF!". When I remove "(5,3)" from the end, it works as I expect it. What is the "(5,3)" supposed to do?
Leo
Sorry, the
(5,3) was an example so that it runs when you cut-and-paste into the formula bar. I.e., create 5 variates with seed 3. It shouldn't be in the formula you copy into the names. Thanks for pointing that out, I've corrected the post.hanjo
Understood, thanks for the update. This is extremely helpful!
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.
Comments
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))
https://docs.google.com/spreadsheets/d/1KwJ7Z_UqUciD42VXdFiiSbVkXPIXGwCzLZHyoNh0Cj4/edit?usp=sharing
