Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randon Number Generation - HELP!
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 we 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randon Number Generation - HELP!
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randon Number Generation - HELP!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randon Number Generation - HELP!
http://sunsite.univie.ac.at/Spreadsite/poptools/
Free POPTOOLS addin. -- Regards, Tom Ogilvy "Nazmul Hasan" wrote in message ... 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 we 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Randon Number Generation - HELP!
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...b;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 Nazmul Hasan wrote: 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 we 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic generation of next number | Excel Discussion (Misc queries) | |||
Help for Random Number Generation | Excel Worksheet Functions | |||
Randon number distribution/generator help please. | Excel Discussion (Misc queries) | |||
I need help with random number generation | Excel Worksheet Functions | |||
Unique ID number generation | Excel Programming |