+ Reply to Thread
Results 1 to 4 of 4

Normal distribution

  1. #1
    Registered User
    Join Date
    02-07-2008
    Posts
    14

    Normal distribution

    What's the consensus about the Box-Müller transform? Is it a statistically and numerically sound way to get normally distributed numbers in Excel?

    'Box-Müller transform
    Dim z1 As Double, z2 As Double, x1 As Double, x2 As Double
    x1 = Rnd
    x2 = Rnd
    z1 = Sqr(-2 * Log(x1)) * Cos(2 * 3.14159265 * x2)
    z2 = Sqr(-2 * Log(x1)) * Sin(2 * 3.14159265 * x2)

    It was about 500 times faster for me than using

    z = Application.WorksheetFunction.NormInv(Rnd, 0, 1) 'or (Rnd, mean, stddev)

    The result seemed to be bell-shaped. I plotted it in Matlab, Excel should add a histogram wizard.
    Last edited by VBA Noob; 03-06-2008 at 10:36 AM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello smaug82,

    Here are 2 good articles that discuss and compare pseudo random number generators and their outcomes for both flat and Gaussian distrubtions.

    Pseudo Random Numbers
    Generating Gaussian Random Nymbers

    Sincerely,
    Leith Ross

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Excel should add a histogram wizard.
    Excel has a FREQUENCY function that makes histograms pretty easy.

  4. #4
    Registered User
    Join Date
    02-07-2008
    Posts
    14
    All right. The polar form was another 25% faster. Since they say it's more robust I'll stick to it. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1