Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
I have some old borrowed code that I use to generate many thousands of random
numbers (investment returns) from a normal distribution, given a mean and standard deviation. It works, except that the sequences aren't really random and I often see the same numbers over and over. I know that the RAND() function was updated to increase randomness, but I'm using VBA's RND function. Does anyone know a way to increase the randomness of the returns produced by this code? Sub Getreturn 'where SD is std dev, meanarith is mean arithmetic return Retn = gauss * SD + meanarith End Sub Function gauss() Dim fac As Double, rt As Double, V1 As Double, V2 As Double 10 V1 = 2 * Rnd - 1 V2 = 2 * Rnd - 1 rt = V1 ^ 2 + V2 ^ 2 If (rt = 1) Then GoTo 10 fac = Sqr(-2 * Log(rt) / rt) gauss = V2 * fac End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
You might try the following Addin.
It is free, and it is excellent. http://sunsite.univie.ac.at/Sp*readsite/poptools/ Joe Adams -----Original Message----- I have some old borrowed code that I use to generate many thousands of random numbers (investment returns) from a normal distribution, given a mean and standard deviation. It works, except that the sequences aren't really random and I often see the same numbers over and over. I know that the RAND() function was updated to increase randomness, but I'm using VBA's RND function. Does anyone know a way to increase the randomness of the returns produced by this code? Sub Getreturn 'where SD is std dev, meanarith is mean arithmetic return Retn = gauss * SD + meanarith End Sub Function gauss() Dim fac As Double, rt As Double, V1 As Double, V2 As Double 10 V1 = 2 * Rnd - 1 V2 = 2 * Rnd - 1 rt = V1 ^ 2 + V2 ^ 2 If (rt = 1) Then GoTo 10 fac = Sqr(-2 * Log(rt) / rt) gauss = V2 * fac End Function . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
what do your random numbers look like.
Do they contain 15 digits? Let's say you have them displayed with only two decimal places - it would be highly likely that you would see duplicate numbers. The concept of unique and Normal are not consistent if your numbers represent an area. for example Normal with a mean of 100 and with a SD of 3, 1.1 would be an area. -- Regards, Tom Ogilvy "Stratuser" wrote in message ... I have some old borrowed code that I use to generate many thousands of random numbers (investment returns) from a normal distribution, given a mean and standard deviation. It works, except that the sequences aren't really random and I often see the same numbers over and over. I know that the RAND() function was updated to increase randomness, but I'm using VBA's RND function. Does anyone know a way to increase the randomness of the returns produced by this code? Sub Getreturn 'where SD is std dev, meanarith is mean arithmetic return Retn = gauss * SD + meanarith End Sub Function gauss() Dim fac As Double, rt As Double, V1 As Double, V2 As Double 10 V1 = 2 * Rnd - 1 V2 = 2 * Rnd - 1 rt = V1 ^ 2 + V2 ^ 2 If (rt = 1) Then GoTo 10 fac = Sqr(-2 * Log(rt) / rt) gauss = V2 * fac End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
I have tested the cycle time of the Rnd function in VBA, and it is
something like 16,700,000. After that many numbers, the entire cycle repeats itself. That may seem like a lot of numbers, but I often have need for more than that. Does anyone have an add-in that generates more random numbers and is pretty quick? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
Did you look at the poptools addin suggest by Joe. It says its random
number generator is slower, but that may be a relative statement and of not much consequence - I don't know, because I haven't used it. http://sunsite.univie.ac.at/Spreadsite/poptools/ -- Regards, Tom Ogilvy "OnceTall" wrote in message oups.com... I have tested the cycle time of the Rnd function in VBA, and it is something like 16,700,000. After that many numbers, the entire cycle repeats itself. That may seem like a lot of numbers, but I often have need for more than that. Does anyone have an add-in that generates more random numbers and is pretty quick? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
The algorithm used by the worksheet RAND() depends on your Excel version
http://support.microsoft.com/kb/q86523/ http://support.microsoft.com/kb/q828795/ It also appears that RNGs in the worksheet, Analysis ToolPak, and VBA each uses a different algorithm. ATP, VBA and pre 2003 worksheet algorithms are not suitable for serious simmulation work. http://groups.google.com/groups?selm...g .google.com implements the 2003 algorithm in VBA. An even better algorithm is the Mersenne Twister http://www.math.sci.hiroshima-u.ac.j...at/MT/emt.html http://www.math.sci.hiroshima-u.ac.j...N/fortran.html http://www-personal.engin.umich.edu/...neTwister.html which is implemented in the freeware NtRand http://www.numtech.com/NtRand/ The simplest theoretically correct way to convert uniform random numbers to normal random numbers is =NORMSINV(RAND()) or Application.NormSInv(RandNum) but I would not recommend this unless you have Excel 2003 which has a greatly improved NORMSINV() function. Alternately you could use the PNormInv() VBA function that I posted in 2001 http://groups.google.com/groups?selm...0no_e-mail.com or Ian Smith's inv_normal() VBA function http://members.aol.com/iandjmsmith/examples.xls that is part of an excellent library of probability functions. Another theoretically correct approach is to use the Box-Muller transformation to get 2*n Normal random numbers from 2*n Uniform random numbers. This is a mathematically exact method that again may magnify the problems of a poor pseudo-random number generator, but at least the functions are implemented to machine accuracy in Excel. My reference books are at the office, so I cannot vouch for these formulas that I got from a Google search, but you take 2 Uniform random numbers (U1, U2) and transform them to 2 Normal random numbers (N1, N2) with formulas like N1 = SQRT(-2*LN(U1)) * COS(2*PI()*U2) N2 = SQRT(-2*LN(U1)) * SIN(2*PI()*U2) Jerry Stratuser wrote: I have some old borrowed code that I use to generate many thousands of random numbers (investment returns) from a normal distribution, given a mean and standard deviation. It works, except that the sequences aren't really random and I often see the same numbers over and over. I know that the RAND() function was updated to increase randomness, but I'm using VBA's RND function. Does anyone know a way to increase the randomness of the returns produced by this code? Sub Getreturn 'where SD is std dev, meanarith is mean arithmetic return Retn = gauss * SD + meanarith End Sub Function gauss() Dim fac As Double, rt As Double, V1 As Double, V2 As Double 10 V1 = 2 * Rnd - 1 V2 = 2 * Rnd - 1 rt = V1 ^ 2 + V2 ^ 2 If (rt = 1) Then GoTo 10 fac = Sqr(-2 * Log(rt) / rt) gauss = V2 * fac End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
Thank you Jerry and Tom.
Tom, I took a quick look at the poptools addin site. It says it is free for non-commercial use. It's not impossible that I would sell my sheet, so I don't want to go that route. Jerry, I am still working my way through your links! I had looked at the Mersenne Twister page before, but the implementations have so many lines of code that I fear it will slow down my sims to a crawl. I downloaded the NtRand freeware some time ago, but it looked to me that it pops a bunch of random numbers into a table in Excel (30,000 at a time?), and then I would have to cycle through the table and then refresh it with another 30,000. That's not out of the question, but I thought going back and forth into Excel from VBA would be slow. I was hoping for something that I could use in VBA similar to the way RND is used. The link to Ian Smith's simple implementation in VBA of the "2003 algorithm" looks promising. It looks simple enough to be fast. Why is it inferior to the Mersenne Twister? Is there a problem with the degree of randomness? If so, how bad is it? (I'm guessing that's a hard thing to quantify.) btw, I'm only looking for a uniform distribution. (I don't need normally distributed numbers). Thanks again for the responses. OnceTall |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation
The Excel 2003 algorithm was published in Applied Statistics in 1982.
At the time it was state of the art, passing all standard tests for randomness and providing a far longer period than any other congruential generator that could be implemented in small word size. In the 1990's, tests for random number generators became much more stringent. Mersene Twister provides a far longer period and better joint independence among sets of random numbers. Jerry OnceTall wrote: Thank you Jerry and Tom. Tom, I took a quick look at the poptools addin site. It says it is free for non-commercial use. It's not impossible that I would sell my sheet, so I don't want to go that route. Jerry, I am still working my way through your links! I had looked at the Mersenne Twister page before, but the implementations have so many lines of code that I fear it will slow down my sims to a crawl. I downloaded the NtRand freeware some time ago, but it looked to me that it pops a bunch of random numbers into a table in Excel (30,000 at a time?), and then I would have to cycle through the table and then refresh it with another 30,000. That's not out of the question, but I thought going back and forth into Excel from VBA would be slow. I was hoping for something that I could use in VBA similar to the way RND is used. The link to Ian Smith's simple implementation in VBA of the "2003 algorithm" looks promising. It looks simple enough to be fast. Why is it inferior to the Mersenne Twister? Is there a problem with the degree of randomness? If so, how bad is it? (I'm guessing that's a hard thing to quantify.) btw, I'm only looking for a uniform distribution. (I don't need normally distributed numbers). Thanks again for the responses. OnceTall |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help for Random Number Generation | Excel Worksheet Functions | |||
random number generation | Excel Discussion (Misc queries) | |||
random number generation | Excel Worksheet Functions | |||
random number generation | Excel Worksheet Functions | |||
I need help with random number generation | Excel Worksheet Functions |