Randomly distributed normal deviates

[James Silverton]

I recently had occasion to want numbers from a normal distribution and was
surprised to find that, AFAIK, Excel does not have a function for that. I
know and used the easy, if clumsy looking, 12-value "sum of random deviates"
method for values with mean 0 and standard deviation 1:
x=(SUM(i-1,12)rand() ) -6
Or writing that slightly differently:
x = -6+RAND()+RAND()+RAND()........(12 values)

I am aware that this method is often suggested on the Net but has anyone had
much experience with it and how well does it work in Excel? Is it worthwhile
thinking about more elaborate functions in, for example, Abramowitz and
Stegun's book?

TIA,

 

[Kevin Stecyk]

James,

Try typing "normal distribution" into XL's help. You will find several
function related to the normal distribution. No point reinventing the
wheel.

normdist
normsdist
norminv
normsinv
standardize
ztest

Regards,
Kevin

 

[James Silverton]

James,

Try typing "normal distribution" into XL's help. You will find several
function related to the normal distribution. No point reinventing the
wheel.

normdist
normsdist
norminv
normsinv
standardize
ztest

Regards,
Kevin

I am aware of these functions but which of them *generates* numbers from a
normal distribution like RAND does for a uniform distribution? To try to put
the problem in perspective, suppose I want to generate an array of 500
numbers distributed so that the FREQUENCY function would give a normal
distribution with a AVERAGE of approximately 0 and an STDEV of 1. The "sum
of 12" method is fast and the results seem to test reasonably well.

Jim.

 

[Kevin Stecyk]

James,

Sorry, I didn't understand your problem well.

There are at least two well known commercial packages that do what you are
asking about. It is used a lot for Monte Carlo simulations.

The packages are as follows:

@Risk - www.palisades.com

Crystal Ball - www.decisioneering.com or I think www.crystalball.com

I have both but tend to use Crystal Ball most often.

My summary is that @Risk has various elements that you can purchase
separately. Crystal Ball tends to be all-in-one.

You can get as many random numbers using a normal distribution, plus many
others, as you wish.

Sorry, I can't answre your question directly as to how to get normal
distributed random numbers from Excel. I use commercial packages for that
purpose.

Regards,
Kevin

 

[Tom Ogilvy]

Previously posted by Jerry Lewis:

Jerry Lewis:

ATP usesv v [ATP = Analysis Toolpak]
=NORMSINV(RAND())
In Excel versions prior to XP, NORMSINV() is so crude that this will
sometimes produce "normal random numbers" that are millions of standard
deviations from the mean.

Alternately, you could use the Box-Muller method
=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())

Both are mathematically exact ways of generating normal random numbers
from uniform random numbers. Excel's RAND() is not a very good uniform
random number generator, hopefully you are not trying to do anything
that is critical.

Jerry

 

[Myrna Larson]

There is no worksheet formula that does this, but Excel does have the ability to generate such a
list. Go to the Tools menu, select Data Analysis, then Random Number Generation.

In the boxes in the next dialog, use 1 for the Number of Variables (i.e. columns), 500 for the
Number of Random Numbers (i.e. rows), Normal for the Distribution, the mean and SD you want, and
where to put the numbers.

 

[David J. Braden]

Actually, what Tom posted, though it was our common understanding, is
incorrect. The ATP is using its own generator, rather than RAND(), then,
as Tom pointed out, NORMSINV().

The ATP generator is *far* worse than RAND(), which itself is terrible.
Upshot is that, while NORMSINV had markedly improved in the last two
versions of Excel, the ATP pseude-random number generator (prng) is
still so bad that I can't help but wonder how the heck someone devised
such a poor algorithm. I'll put out a seperate post in *.excel.misc
today with VBA code so folks can see this.

Here's an easy solution: download the costless add-in PopTools (Google
search for it). It has a superb prng, and will return arrays of
variates, which makes for mighty fast execution, much faster than if you
roll your own in VBA.

HTH
Dave Braden

 

[James Silverton]

Previously posted by Jerry Lewis:

Jerry Lewis:

ATP usesv v [ATP = Analysis Toolpak]
=NORMSINV(RAND())
In Excel versions prior to XP, NORMSINV() is so crude that this will
sometimes produce "normal random numbers" that are millions of standard
deviations from the mean.

Alternately, you could use the Box-Muller method
=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())

Both are mathematically exact ways of generating normal random numbers
from uniform random numbers. Excel's RAND() is not a very good uniform
random number generator, hopefully you are not trying to do anything
that is critical.

Jerry
--
Regards,
Tom Ogilvy

I recently had occasion to want numbers from a normal distribution and was
surprised to find that, AFAIK, Excel does not have a function for that. I
know and used the easy, if clumsy looking, 12-value "sum of random deviates"
method for values with mean 0 and standard deviation 1:
x=(SUM(i-1,12)rand() ) -6
Or writing that slightly differently:
x = -6+RAND()+RAND()+RAND()........(12 values)

I am aware that this method is often suggested on the Net but has anyone had
much experience with it and how well does it work in Excel? Is it worthwhile
thinking about more elaborate functions in, for example, Abramowitz and
Stegun's book?

Thanks, all I wanted to do was to produce a set of numbers to illustrate a
discussion. I only used Excel because that was what was handy. I know only
too well how bad tempered discussions of the "best" pseudo-random numbers
can become and for real work, I have generally used functions derived from
either Marsaglia or Knuth. I should have thought of NORMSINV(RAND()). With
Office XP, it seems to calculate and test as well as the "Sum of 12", tho'
for as few as 500 numbers, speed is not very relevant.

Jim.