Generate random number.

[Attilio Gelosa]

Hy everybody,

this is my problem: I have to generate random numbers, using VBA, from
different distribution functions... Rand (and randomize...) generates
numbers only from a "Uniform distribution" (well... Microsoft says that it's
a "pseudo-uniform" distribution, but this isn't a problem...). How can
ganerate numbers from a Normal (=Gaussian) and a Exponential distribution
using VBA?

Thank you in advance...

Bye Attilio.

[Dave Martin]

This formula uses the Box-Mueller Monte-Carlo simulation to yield normal
random numbers:

=$A$1+$A$2*(((-2*LN(RAND()))^0.5)*COS(2*PI()*RAND()))

Cell A1 would contain the Mean or average while cell A2 would contain the
desired standard deviation or Sigma. You can copy this down a column to
get a normal random distribution.

Hope this helps.

Don't know how to do an exponential.

"Attilio Gelosa" <attili...@libero.it> wrote in message
news:a0uhkl$n3ohf$1...@ID-59998.news.dfncis.de...

[Bob Weber]

Attilio -

=norminv(rand(),mean,stdev) for a normal variate, and
=-ln(1-rand())/lambda for an exponential variate.

(Generally, if F(x) is the cumulative distribution of the desired variate,
F[inverse](rand()) will simulate the variate itself.)

- Bob

"Attilio Gelosa" <attili...@libero.it> wrote in message
news:a0uhkl$n3ohf$1...@ID-59998.news.dfncis.de...

[Dana DeLouis]

Hello. Here is a slightly different way to do it in VBA.
You need a Library reference set to ATPVBAEN.
Apparently, using "Application.DisplayAlerts = False"
does not work when data on a sheet is about to be overwritten.
(Don't know why...Anybody ???). Therefore, I just cleared the output area
to avoid that message.
You can adjust the Mean & SD setting. The Output Range can also be more
than 1 column.
I am not sure how you would do an Exponential distribution thou. HTH.
Dana DeLouis.

Sub Demo()
' Dana DeLouis
Const FxRandom As String = "ATPVBAEN.XLA!Random"
Const NormalDistribution As Long = 2
Const Mean As Double = 0 'Mean
Const SD As Double = 1 'Standard Dev

Dim OutRange As Range
Set OutRange = [A1:A10]

[OutRange].ClearContents
Application.Run FxRandom, OutRange, , , NormalDistribution, , Mean, SD
End Sub

--
Dana DeLouis Windows Me & Office XP

"Attilio Gelosa" <attili...@libero.it> wrote in message
news:a0uhkl$n3ohf$1...@ID-59998.news.dfncis.de...

[Jerry W. Lewis]

Using the inverse function is mathematically exact, but often
numerically poor. It tends to magnify problems with either the uniform
random number generator or the inverse function.

NormInv() and NormSInv() are particularly poor (in Excel versions prior
to XP), and will generate an unacceptably large fraction of "normal
random numbers" that are several million standard deviations away from
the mean!

Jerry

[Nico Sterk]

A good theoretical source is
http://www.itp.phys.ethz.ch/lectures/RGP/PDF/week5b.pdf

HTH

--
Nico Sterk
Venuslaan 71
5632 HA Eindhoven
Netherlands
+31 40 242 9317
+31 6 29 10 70 15
n.s...@chello.nl
http://members.tripodnet.nl/excelsoftware

"Attilio Gelosa" <attili...@libero.it> wrote in message
news:a0uhkl$n3ohf$1...@ID-59998.news.dfncis.de...

[Nico Sterk]

Also http://www.wr.inf.ethz.ch/education/pr/files/u8/bm.pdf

--
Nico Sterk
Venuslaan 71
5632 HA Eindhoven
Netherlands
+31 40 242 9317
+31 6 29 10 70 15
n.s...@chello.nl
http://members.tripodnet.nl/excelsoftware
"Attilio Gelosa" <attili...@libero.it> wrote in message
news:a0uhkl$n3ohf$1...@ID-59998.news.dfncis.de...

[David J. Braden]

Jerry,
It's worse than that even with the latest versions for NORMINV. At
first I thought things had been fixed; they are still awful, though.
Where we used to see Normal deviates generated out past 5000 (don't
remember the magnitude; sorry) all too frequently (and never higher),
now the inverse is still messed up in the tails, but gives errors that
are less obvious, but nonetheless practically siginificant. Try it out
on the latest versions on either platform and you'll see what I mean.
Truly pathetic.

Regards,
Dave Braden

In article <3C332828.2070302@no_e-mail.com>, Jerry W. Lewis
<post_a_reply@no_e-mail.com> wrote:

--
FAQ and other goodies: http://www.cpearson.com/excel/newposte.htm
More FAQs: http://j-walk.com/ss/excel/faqs/index.htm
More info and links: http://j-walk.com/ss/

[David J. Braden]

Dana,
Your idea is just an indirect way to call NORMINV; it requires one to
load the ATP, which is a negative (IMHO). NORMINV is itself messed up.

Regards,
Dave Braden

In article <OPV6dg5kBHA.2080@tkmsftngp07>, Dana DeLouis
<ng_...@hotmail.com> wrote:

--

[Jerry W. Lewis]

Can you be more specific about the problem? Mathematically, if U
follows the uniform(0,1) distribution, then GInv(U) follows the G()
distribution. If Excel's "normal" random numbers are still not adequate
in XP, then it must be due to one or more of the following:

1. RAND() is not an adequate approximation to uniform random numbers

2. The improved accuracy to NORMSINV() in Excel XP is still not
adequate, but machine accuracy would be

3. Machine accuracy for RAND() and NORMSINV() still are still too
discrete in the to adequately approximate normal tails

The second potential reason could easily be fixed using the PNormInv
algorithm I posted to microsoft.public.excel.misc in September under
your thread "Normal Distribution"

http://groups.google.com/groups?selm=3BB620CB.40404%40no_e-mail.com

The first potential reason would impact the correlation structure of
multiple "random numbers," and so might cause problems with the
Box-Muller algorithm also. It could be improved with a better
pseudo-random number generator, but the nature of pseudo-random numbers
is that it cannot be completely eliminated.

The third potential reason would likely impact the tails of any
distribution over an infinite or half-infinite range. It might also
impact the Box-Muller algorithm. If this is a significant problem, then
alternate approaches, such as Marsaglia's ziggurat method
http://www.jstatsoft.org/v05/i08/
would need to be used for converting uniform random numbers into other
distributions.

Jerry