random number generator

[Guest]

I'm trying to obtain the following:

A 30 column x 1000 row table of normal distribution of random item responses
ranging from 1-4.

I'm not sure how to control the "Normal" output of the # generator, and I
can't figure out how to generate only natural numbers (no decimals). I can
format the numbers to 0 decimal places, but the "truth" of the numberis still
listed as 3.1223.. which isn't a response option on my tests. (that's a joke)

 

[James Silverton]

johnny wrote on Tue, 24 Apr 2007 09:02:07 -0700:

jv> A 30 column x 1000 row table of normal distribution of
jv> random item responses ranging from 1-4.

RANDBETWEEN(1,4) perhaps?

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Mike Middleton]

johnny vino -

One way to get normal random values is to use worksheet functions

=NORMINV(RAND(),Mean,StDev)

One way to get integer values approximately normal is

=INT(NORMINV(RAND(),Mean,StDev))

But, if you want only integer values 1,2,3,4, you need a discrete
approximation of the continuous normal distribution. Conceptually, there are
several ways to obtain the approximation. Or, you could just arbitrarily
assign probability 0.15, 0.35, 0.35, 0.15 to the values 1,2,3,4,
respectively.

- Mike
http://www.MikeMiddleton.com

 

[Guest]

In each cell: =INT(RAND()*4)+1

 

[James Silverton]

Eric wrote on Tue, 24 Apr 2007 10:00:02 -0700:

E> "johnny vino" wrote:

??>> I'm trying to obtain the following:
??>>
??>> A 30 column x 1000 row table of normal distribution of
??>> random item responses ranging from 1-4.
??>>
??>> I'm not sure how to control the "Normal" output of the #
??>> generator, and I can't figure out how to generate only
??>> natural numbers (no decimals). I can format the numbers
??>> to 0 decimal places, but the "truth" of the numberis still
??>> listed as 3.1223.. which isn't a response option on my
??>> tests. (that's a joke)

Sorry, I missed the word "normal". I suspect that suggestions
using NORMINV could be used to get *adequate* values even if it
is not the greatest generator for normal distributions. I'm not
an expert but I'm going to have to take a look to see if the
problem has been discussed in the statistical literature.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Mike Middleton]

James Silverton -

The NORMINV and RAND (and some other) worksheet functions were improved in
Excel 2003. For information, browse to www.microsoft.com, and search for
"excel rand norminv" (without the quotes), or search for something similar.

- Mike
http://www.MikeMiddleton.com

 

[James Silverton]

Mike wrote on Tue, 24 Apr 2007 23:02:55 -0700:

MM> The NORMINV and RAND (and some other) worksheet functions
MM> were improved in Excel 2003. For information, browse to
MM> www.microsoft.com, and search for "excel rand norminv"
MM> (without the quotes), or search for something similar.

MM> - Mike
MM> http://www.MikeMiddleton.com

Thanks, that's interesting but I'm afraid I'm stuck with Excel
2002 for the moment but fortunately, I usually use random
numbers for interest rather than practical purposes. I use the
Box-Muller method to get numbers from a normal distribution and
that seems OK and quite rapid. Correct me please if I am wrong
but didn't the original poster really want numbers from a
Poisson distribution? I can't see how to implement the
programming eg.
http://www.cedarcreek.umn.edu/tools/utility/rpoisson.d.pdf in
Excel.



James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Mike Middleton]

James -

The original poster wanted "A 30 column x 1000 row table of normal
distribution of random item responses ranging from 1-4."

Regarding Poisson, an outstanding collection of probability and cumulative
inverse functions is Ian Smith's library of VBA functions from
http://members.aol.com/iandjmsmith/examples.xls

- Mike

 

[James Silverton]

Mike wrote on Wed, 25 Apr 2007 09:47:23 -0700:

MM> The original poster wanted "A 30 column x 1000 row table of
MM> normal distribution of random item responses ranging from
MM> 1-4."

MM> Regarding Poisson, an outstanding collection of probability
MM> and cumulative inverse functions is Ian Smith's library of
MM> VBA functions from
MM> http://members.aol.com/iandjmsmith/examples.xls

Mike!

Thanks again. I'll enjoy reading that when I have a chance.

Jim.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not