"Random Number Generation" Window?

[Mike]

Hi everyone,

Using Tools/Data Analysis/ and then selecting "Random Number
Generation", you get a window called "Random Number Generation"

However, is there an online reference on how to use this window
efficiently? I tried "Help" but got not much information at all!

Thanks,
Mike

 

[Bob Phillips]

Mike,

There is a Help button. Did that not help?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike]

Bob,

Not at all and I was suprised I couldn't find even fair details. Also,
no example on how to fill some of the options correctly? For xample, on
how to fill the parameters block, I tried different ways on how to
enter the "Value and Probability Input Range" but none worked!

Mike

 

[Bob Phillips]

Mike,

I am afraid that this is not my field, but a cursory look on the web didn't
offer any greater detail that I could see. Sorry, not a lot of help.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Mike Middleton]

Mike -

... Also, no example on how to fill some of the options correctly? For
xample, on how to fill the parameters block, I tried different ways on how
to enter the "Value and Probability Input Range" but none worked! <

The appropriate parameters depend on the type of distribution you choose.

If you choose "Discrete," the parameters must be values and probabilities on
a worksheet, as described in Help:

"Discrete
Characterized by a value and the associated probability range. The range
must contain two columns: The left column contains values, and the right
column contains probabilities associated with the value in that row. The sum
of the probabilities must be 1."

If you have questions about the discrete distribution or other types of
distributions, please post specific questions.

Also, there are other ways to obtain random numbers in Excel, including
dynamic formulas using the RAND worksheet function and specialized functions
in tryout versions of Monte Carlo simulation add-ins (like my RiskSim
available at www.treeplan.com). Depending on your application, these dynamic
methods may be better than the static values produced by the Random Number
Generation tool.

- Mike
www.mikemiddleton.com

 

[Tom Ogilvy]

Analysis toolpak functions have fairly extensive documenation in the the xl4
macro help file.



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


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

Are the Old help style files. There are later CHM versions, but I don't
have the URL's handy.

Anyway for example, the RANDOM function: (easier to read in the help file)

Macro Sheets Only
Fills a range with independent random or patterned numbers drawn from one of
several distributions.
If this function is not available, you must install the Analysis ToolPak
add-in.
RANDOM provides six different random distributions and one patterned data
option. Because the distributions require different argument lists, there
are seven syntax forms for RANDOM.

Syntax 1

Uniform distribution

RANDOM(outrng, variables, points, distribution, seed, from, to)
RANDOM?(outrng, variables, points, distribution, seed, from, to)

Syntax 2

Normal distribution

RANDOM(outrng, variables, points, distribution, seed, mean, standard_dev)
RANDOM?(outrng, variables, points, distribution, seed, mean, standard_dev)

Syntax 3

Bernoulli distribution

RANDOM(outrng, variables, points, distribution, seed, probability)
RANDOM?(outrng, variables, points, distribution, seed, probability)

Syntax 4

Binomial distribution

RANDOM(outrng, variables, points, distribution, seed, probability, trials)
RANDOM?(outrng, variables, points, distribution, seed, probability, trials)

Syntax 5

Poisson distribution

RANDOM(outrng, variables, points, distribution, seed, lambda)
RANDOM?(outrng, variables, points, distribution, seed, lambda)

Syntax 6

Patterned distribution

RANDOM(outrng, variables, points, distribution, seed, from, to, step,
repeat_num, repeat_seq)
RANDOM?(outrng, variables, points, distribution, seed, from, to, step,
repeat_num, repeat_seq)

Syntax 7

Discrete distribution

RANDOM(outrng, variables, points, distribution, seed, inprng)
RANDOM?(outrng, variables, points, distribution, seed, inprng)

Outrng is the first cell (the upper-left cell) in the output table or the
name, as text, of a new sheet to contain the output table. If FALSE, blank,
or omitted, places the output table in a new workbook.

Variables is the number of random number sets to generate. RANDOM will
generate variables columns of random numbers. If omitted, variables is equal
to the number of columns in the output range.

Points is the number of data points per random number set. RANDOM will
generate points rows of random numbers for each random number set. If
omitted, points is equal to the number of rows in the output range. Points
is ignored when distribution is 6 (Patterned).

Distribution indicates the type of number distribution.

Distribution Distribution type

1 Uniform
2 Normal
3 Bernoulli
4 Binomial
5 Poisson
6 Patterned
7 Discrete

Seed is an optional value with which to begin random number generation.
Seed is ignored when distribution is 6 (Patterned) or 7 (Discrete).

From is the lower bound.

To is the upper bound.

Mean is the mean.

Standard_dev is the standard deviation.

Probability is the probability of success on each trial.

Trials is the number of trials.

Lambda is the Poisson distribution parameter.

Step is the increment between from and to.

Repeat_num is the number of times to repeat each value.

Repeat_seq is the number of times to repeat each sequence of values.

Inprng is a two-column range of values and their probabilities.

 

[David J. Braden]

Mike,
I'm surprised that you, Tom and Bob are abetting what is surely one of the
worst random-number generators written, and still maintained!!! (Gentle
rib.) The ATP rng is horrible, dynamic/static issues aside. It generates
both 0's and 1's, and with alarming frequency. In relatively small samples,
it will repeat values *a lot*. By all means, keep the OP away from the
thing (IMO). Your low-key allusion to much better solutions might be why
you got tenure and I didn't (vbg).

Another add-in to mention is Pop-tools, which has an absolutely wonderful
rng, as well as a lot of useful functions good for MC simulation.

Regards from Mtn View,
Dave Braden

 

[Bob Phillips]

Mr Braden,

I really must protest

I never in any way abetted or endorsed the PRND in Excel, just tried to help
the OP get the help he sought from that function.

I know about PopTools, I have even suggested it in response before.

Nice to see you here again BTW

Regards

Bob

 

[Mike Middleton]

Dave -

Aargh! You're right. I exhibited extreme laxity. Or, I could lamely claim
that I was trying to provoke one of your insightful missives. Thanks for
chiming in.

- Mike

 

[David J. Braden]

Dear Bob,
It's funny to me that you were careful to add the "p" to "rng"; I had the
thought after I posted that if *ever* there were a "pseudo-"random number
generator, well, the one coded up for ATP certainly qualifies. Or should it
instead be termed something else? I'll leave it to Harlan to come up with an
appropriate acronym and description (e.g., Sublimely Hilarious Integer-based
Testcode)

I sure as heck did not intend to offend you guys; I hope you took the post
in the same spirit with which I intended it--- mainly, it's fun to drop in
and see what you folks are up to.

All the best,
Dave B

 

[Bob Phillips]

I think we all did David, I added a smiley to mine

Regards

Bob

Is it integer based? <g>

 

[Mike]

Tom,

What you are saying above is interesting but found no trace to it! As
far as I know there is no RANDOM function, there is RAND or RANDBETWEEN
but not RANDOM!? I have "Analysis Toolpak" active however but couldn't
find what you are saying!

Am I missing something?

Please advise? Thanks,
Mike

 

[Tom Ogilvy]

The actual name in the Analysis toolpak is random:

Evidence, I recorded a macro will using it:

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$A$9:$C$20"),
, _
, 1, 3, 0, 10

Note the Term RANDOM

ATPVBAEN means Analysis ToolPak VBA ENglish

 

[Mike]

Tom,

Mine is Excel 2002. I am assumung because I don't have xl4 macro help
file, mentioned above, I am having difficulty following what you are
saying!

More information is needed Tom..........

Thanks
Mike

 

[Bob Phillips]

Mike,

Tom gave you the page from the help file for the Random function. You can
download those help files from the links Tom gave you if you need further
information. I may be putting words in Tom's mouth, but I don't think there
is much more he can tell you, it's now up to you to deploy that information.

--

HTH

RP
(remove nothere from the email address if mailing direct)