Non-random numbers generated by excel's data analysis random gener

[Allie]

Hi,

I've been trying to generate random numbers using excel's random number
generator in the analysis toolpak. I'm using Uniform number generation
between 0 and 1, and I am using a random seed, as I would like to be able to
re-generate the same numbers in the future.

Using seeds from 1 to 10, when i fill a range of 16 cells with random
numbers, the first cell filled is always the lowest. This is an extremely
unlikely circumstance with a true RN generator. I just wanted to raise this
flag, as it could be affecting others without their knowledge.

Thanks,
Allie

 

[Peo Sjoblom]

Copy this formula down

=INT(RAND()*(10-1)+1)

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Allie]

Thanks for your reply, Peo. I'm not looking for a workaround (which I've
already figured out), I'm interested in letting people, and microsoft, know
about this bug.

Thanks,
Allie

 

[Mike Middleton]

Allie -

See "Description of the effects of the improved statistical functions for
the Analysis ToolPak in Excel 2003 and in Excel 2004 for Mac," at

http://support.microsoft.com/default.aspx?scid=kb;en-us;829208

specifically, the "Random Number Generation" section.

- Mike
www.mikemiddleton.com

 

[joeu2004]

I've been trying to generate random numbers using excel's random number
generator in the analysis toolpak. I'm using Uniform number generation
between 0 and 1, and I am using a random seed, as I would like to be able to
re-generate the same numbers in the future.

Using seeds from 1 to 10, when i fill a range of 16 cells with random
numbers, the first cell filled is always the lowest. This is an extremely
unlikely circumstance with a true RN generator.

Not at all.

First, because you are seeding the RNG, it is not
"a true RNG" at all. The sequence is predictable
and repeatable, which is your intention.

Second, if we knew the RNG algorithm, it might not
be surprising at all that a low seed (1 to 10) might
generate a very low number initially, perhaps even
the lowest value a small set (16) of numbers.

In fact, if you increase the set to 100 with a seed
of 10, the first number is not the smallest. The
same is true of a set of 16 numbers if you choose a
large seed, e.g. 10000.

MS Excel documentation should offer some guidance for
choosing a seed. It can greatly affect not only the
range of numbers generated, but also the apparent
randomness (distribution over the range) of at least
the first small set of numbers. I do not find any
such guidance in the Help text.

In any case, all of this is common to all RNGs. An
RNG simply uses a recursive mathematical formula.
For example, a linear congruential method might use
the formula X[n+1] = (a*X[n] + c) mod m, where X[0]
is the seed or a deterministic function of the seed
(apparently the latter for the ATP RNG, since its
seed must be an integer), and a, c and m are chosen
by the RNG designer, hopefully based on the plethora
of research literature on the subject. For an
introduction, see Knuth, Art of Computer Programming,
vol 2 (Seminumerical Algorithms).

I hope this gives you some insight into your observation.
If it bothers you (I don't know why it would), choose
a different seed or generate a larger set of numbers.

 

[joeu2004]

Copy this formula down
=INT(RAND()*(10-1)+1)

Obviously you do not understand what a "seed" is for an
RNG.

Allie is not trying to generate random integers between
1 and 10, which your expression does. In fact, she said
she wants to generate random real numbers between 0 and 1.

A "seed" is a value that is used to initialize an RNG
so that the sequence is repeatable -- as Allie said.
Your expression generates a non-repeatable sequence of
numbers -- hopefully ;-).

 

[D Hilberg]

Anyone who still uses numerical methods to generate random numbers is
living in a state of sin.

(according to John von Neuman)

- D Hilberg

(Have you tried measuring the decay of a radioactive element?)

 

[Jerry W. Lewis]

Anyone who still uses numerical methods to generate random numbers is
living in a state of sin.

(according to John von Neuman)

- D Hilberg

(Have you tried measuring the decay of a radioactive element?)

And you measure the decay of radioactive elements from your living room how?

pRNG algorithms have progressed greatly since von Neumann's time. Excel
2003's RAND(), which is a vast improvement, is still two decades out of
date. The Mersenne Twister is quite a good pRNG

http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html
http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/VERSIONS/FORTRAN/fort...
http://www-personal.engin.umich.edu/~wagnerr/MersenneTwister.html

which is implemented in the freeware NtRand

http://www.numtech.com/NtRand/

Jerry

 

[joeu2004]

Anyone who still uses numerical methods to generate random
numbers is living in a state of sin.
[....]
(Have you tried measuring the decay of a radioactive element?)

At the risk of giving credance to what might have been
intended as levity ....

Yes, there are hardware-based RNGs. These are becoming
common-place on "mainframes" (technical computers). They
might be available as add-ons for PCs. I did not think
they are commonly built into PCs, though. Are they?

In any case, the OP asked about a seeded RNG. By
definition, a seeded RNG is deterministic (repeatable),
not "random" at all (although we hope a large sampling
has the appearance of "randomness"). And whether you
rely hardware or software, the first number of a seeded
RNG will depend on the seed, by definition.

The purpose of my posting was to provide a basic
understanding of seeded RNGs so that the OP could
understand why the first number in a small sampling
__might__ "always" be the smallest value, without the
illusion of a defect. And in fact, my point was: it
will __not__ always be the smallest, if you choose
either the seed or the sample size appropriately.

 

[David J. Braden]

Hey Mike!
OK, OK, others interested in this kind of stuff---
The link http://support.microsoft.com/kb/828795 glosses over Excel's latest
incarnation of RAND()

(1) We now have a doc of what MS intends. Can anyone map back into the longs
from doubles to run the PRNG through Marsaglia's DIEHARD? (I can't) The
coders might be trying to fix a long established problem (I started
complaining in 1985). Do we *know* that they actually implemented what they
intended this last go-round (as opposed to what shipped with Excel 11) with
the latest SP? Also, *when* will they get a seed into the thing? (see note)

(2) Microsoft's note re ATP PRNG is absolute BS. Generate a few thousand
variates, and you will see repeats, including a number of 0's and 1's.

(3) What's up with VBA's RND? Any sign of an impending upgrade?

Regards,
Dave B

 

[Dana DeLouis]

Hi Alice. I believe this is a known problem. The first number in the list
follows a linear relationship with the seed.
With a 16 number output, the first number output runs from about 0 to 1 with
seeds 1 to about 10022. (It's a near perfect correlation. R^2 =
1.000000)With a seed of 10023, the first number drops back to near zero and
repeats the cycle.
So, when you seed with low numbers, there is little chance that another
number will be lower than the first one. As the seeds get larger, the first
number gets larger, and there are more chances that other numbers will be
lower than the first.

I was just curious, so I plotted the percentages that the first number was
the lowest out of 16. The plot starts out near 95% - 100% for a few hundred
numbers, and then exponentially decays to about 8% when reaching the largest
seed of 32767. Because of the "Bug", (err...I mean feature) it will never
get to the expected 6.25% (1/16).

If you would like to see a listing of the first number in the output, here
is a macro. There is no need to see all 32767, as the first few hundred
will show you the relationship.
Set a vba library reference to "atpvbaen.xls"
This takes a few minutes to run.

Here, the "C" is used as a seed number, and as a column pointer to store the
first output cell.

Sub Curious()
Dim C As Long
Const Random As String = "ATPVBAEN.XLA!Random"

[A:C].Clear

For C = 1 To 11000 '32767
[A1:A16].Clear
Run Random, Cells(1, 1), 1, 16, 1, C, 0, 1
Cells(C, 3) = Cells(1, 1)
Application.StatusBar = C
Next C
End Sub

HTH :>)