Random Number Generator Problem

[Chris Peck]

Please tell me if I am doing something stupid, but I having trouble
generating random numbers. If I select Tools: Data Analysis: Random
Number Generator, then select Normal Distribution, Mean 0, Standard
Deviation 1, and then I select my range of output, I get the SAME
numbers everytime!!! What's worse, the top left number is 9.XXX
(considering the mean is 0 and standard deviation is 1, that is pretty
much impossible).

I am using Excel 2004 (Mac)

Thanks
Chris

 

[Rob]

Please tell me if I am doing something stupid, but I having trouble
generating random numbers. If I select Tools: Data Analysis: Random
Number Generator, then select Normal Distribution, Mean 0, Standard
Deviation 1, and then I select my range of output, I get the SAME
numbers everytime!!! What's worse, the top left number is 9.XXX
(considering the mean is 0 and standard deviation is 1, that is pretty
much impossible).

I am using Excel 2004 (Mac)

Thanks
Chris

In WinXL I use "=RANDBETWEEN(bottom value,top value)" to generate random
numbers but this function is not available in XL2004? Analysis ToolPak
is installed on both platforms.

Rob

 

[JE McGimpsey]

Please tell me if I am doing something stupid, but I having trouble
generating random numbers. If I select Tools: Data Analysis: Random
Number Generator, then select Normal Distribution, Mean 0, Standard
Deviation 1, and then I select my range of output, I get the SAME
numbers everytime!!! What's worse, the top left number is 9.XXX
(considering the mean is 0 and standard deviation is 1, that is pretty
much impossible).

I am using Excel 2004 (Mac)

All common RNGs are really pseudo-random-number-generators, using
functions that (in theory) don't repeat values for a very large number
of iterations. However, if you feed the same number into the RNG's
function, it will always produce the same result.

Each operation of the ATP's RNG has an optional parameter that allows
you to start the function at the same point, and thus get the same
values time and again. It's called the Random Number Seed. If you clear
that box, the generator will use a different seed every time (probably
based on the internal clock.

See "Options in the Random Number Generation dialog box" in XL Help.

Note, however, that for serious statistical work, the ATP RNG is pretty
lousy, generating repeated numbers and mediocre distributions.

Look in the excel groups archives:

http://groups.google.com/advanced_group_search?q=group:*excel*

for posts on generating normal distributions. For discussion on the
inadequacies of XL's various RNGs (i.e., ATP, RAND(), VBA's Rnd()), look
for posts by David Braden and Jerry Lewis, among others.

FWIW, XL04's RAND() appears to be much improved over previous versions.
ATP's RNG and RANDBETWEEN() aren't.

 

[JE McGimpsey]

In WinXL I use "=RANDBETWEEN(bottom value,top value)" to generate random
numbers but this function is not available in XL2004? Analysis ToolPak
is installed on both platforms.

RANDBETWEEN is part of the ATP and works in XL04.

What happens when you try to use it?

 

[Jim Gordon MVP]

Hi,

The random number feature in Excel 2004 works differently from all other
versions of Excel. There's a detailed explanation of this somewhere in
Microsoft's support site, but at the moment I can't find that information
for you.

I'll keep looking.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.


----------

 

[Jim Gordon MVP]

Well, I found the article for Excel 2003 here:
http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003

I think there's another article explaining that Excel 2004 has further
refinements to the random number generator. If I find that one I'll post
back.

-Jim

 

[Rob]

RANDBETWEEN is part of the ATP and works in XL04.

What happens when you try to use it?

#NUM!

 

[JE McGimpsey]

RANDBETWEEN is part of the ATP and works in XL04.

What happens when you try to use it?

#NUM![/QUOTE]

Hmmmm... I can't reproduce this at all unless my inputs reference a cell
that returns #NUM!, or use a value that is out of range, like 1E+307^2.

What are the inputs? If they're cells, what are the cell values?