Randon Number Generation - HELP!

[Nazmul Hasan]

Hi everyone,

I have been using the Random Number Generation (RNG) tool that comes with
excel to generate numbers for an investigation i am doing for my masters
degree dissertation.

I am very stuck now as i have discovered that the RNG is not really random,
and i have had many multiple results that have repeated themselfs.

I have been using the RNG tool that comes as part of the Analysis ToolPak.
The RNG is accessed via Tools, Data Analysis, Random Number Generation.

The setting i had were:
No. of variables - 4
Number of Random Numbers - this changed with the user choice, say,
40
Distribution - Normal
Parameters: - Mean 0, Standard Deviation 1

No random seed chosen (i acutally don't know what it does)

Output range - A1

Can anyone help me either write a proper RNG tool that i could use in Excel,
with the above setting. Alternatively, does anyone know where i may be able
to obtain in a (free) add-in for excel that will do this task better then
Excel's own RNG.

I am on a extremely tight deadline with my dissertation, any help or advice
is very welcome and is much appreciated.

Thank you all in advance.

Regards

Nazmul Hasan

 

[jamon]

No random seed chosen (i acutally don't know what it does)

This is your problem. The random seed, if it is the same, is what gets
everything started. If you seed with 1 and print out a list of random
numbers and then seed with 1 again and print out more they will match.

I would recommend putting a timer variable or something as the random seed
(as the time they run the macro is fairly random). Maybe someone else can
elaborate because I'm not totally sure on the syntax.

-Jamon

 

[Michael R Middleton]

Nazmul Hasan -

Alternatively, does anyone know where i may be able to obtain in a (free)

add-in for excel that will do this task better then Excel's own RNG. <

Download Monte Carlo simulation add-in file risk230e.xla and documentation
file risksim.pdf (with explanation of seed) from the "Decision Modeling
Using Excel" page of my university web site http://www.usfca.edu/~middleton,
and use its RANDNORMAL(mean,stdev) function

And please provide some feedback.

- Mike

www.mikemiddleton.com

 

[Tom Ogilvy]

http://sunsite.univie.ac.at/Spreadsite/poptools/

Free POPTOOLS addin.

 

[Jerry W. Lewis]

What version of Excel? ATP RNG tool is bad in all versions, but the
RAND() worksheet function is decent in 2003, provided that you have
service patches at least throught March 2004. The algorithm used in
2003 is documented
http://support.microsoft.com/default.aspx?scid=kb;en-us;828795
and could be implemented as a UDF in earlier versions.

To go from uniform random numbers to normal random numbers, ATP uses
NORMINV(rand,mu,sigma). This is mathematically correct, but numerically
lousy in versions pror to 2003, because of inadequacies in the
implementation of NORMINV().
http://members.aol.com/iandjmsmith/examples.xls
contains a vba implementation called inv_normal() that is better than
the new NORMSINV() in 2003.

Other methdods methods for generating normal random variables, include
the Box-Muller
www.wr.inf.ethz.ch/education/pr/files/u8/bm.pdf
and Marsaglia's ziggurat algorithm
www.stanford.edu/class/cs138/random.pdf

For serious simulation work, you would probably be better served to skip
Excel and use R
http://www.r-project.org/
an open source statistical program that uses a much better uniform RNG
than Excel 2003.

Jerry