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
Mike,
There is a Help button. Did that not help?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Mike" <mas_it_2000@yahoo.com> wrote in message
news:1122922374.799052.127700@g47g2000cwa.googlegroups.com...
> 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,
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
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" <mas_it_2000@yahoo.com> wrote in message
news:1122925116.740282.23760@f14g2000cwb.googlegroups.com...
> 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
>
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
Analysis toolpak functions have fairly extensive documenation in the the xl4
macro help file.
http://support.microsoft.com/default...b;en-us;143466
xlmacr8.hlp
http://support.microsoft.com/default...b;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.
--
Regards,
Tom Ogilvy
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23O9eIUulFHA.3312@tk2msftngp13.phx.gbl...
> 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" <mas_it_2000@yahoo.com> wrote in message
> news:1122925116.740282.23760@f14g2000cwb.googlegroups.com...
> > 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
> >
>
>
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
"Mike Middleton" <middleton@mikemiddleton.com> wrote in message
news:uku4w4ulFHA.1204@TK2MSFTNGP12.phx.gbl...
> 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
>
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
"David J. Braden" <dbraden@nomail.invalid> wrote in message
news:etZbNK5lFHA.3336@tk2msftngp13.phx.gbl...
> 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
>
> "Mike Middleton" <middleton@mikemiddleton.com> wrote in message
> news:uku4w4ulFHA.1204@TK2MSFTNGP12.phx.gbl...
> > 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
> >
>
>
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" <dbraden@nomail.invalid> wrote in message
news:etZbNK5lFHA.3336@tk2msftngp13.phx.gbl...
> 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
>
> "Mike Middleton" <middleton@mikemiddleton.com> wrote in message
> news:uku4w4ulFHA.1204@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>
>
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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uSI4DS6lFHA.3828@TK2MSFTNGP12.phx.gbl...
> 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
>
> "David J. Braden" <dbraden@nomail.invalid> wrote in message
> news:etZbNK5lFHA.3336@tk2msftngp13.phx.gbl...
>> 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
>>
>> "Mike Middleton" <middleton@mikemiddleton.com> wrote in message
>> news:uku4w4ulFHA.1204@TK2MSFTNGP12.phx.gbl...
>> > 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
>> >
>>
>>
>
>
I think we all did David, I added a smiley to mine
Regards
Bob
Is it integer based? <g>
"David J. Braden" <dbraden@nomail.invalid> wrote in message
news:%23GEZl78lFHA.3816@tk2msftngp13.phx.gbl...
> 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
>
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
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
--
Regards,
Tom Ogilvy
"Mike" <mas_it_2000@yahoo.com> wrote in message
news:1123094304.360782.126810@g49g2000cwa.googlegroups.com...
> 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,
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
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)
"Mike" <mas_it_2000@yahoo.com> wrote in message
news:1123102669.999245.97160@g47g2000cwa.googlegroups.com...
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks