Generating random numbers with Excel

The methods listed below work well with Excel 2003 and later, but should not be used with earlier versions of Excel. 

Before deciding to use Excel to generate random numbers, check out these GraphPad free web calculators. 

Random numbers from a uniform distribution

The RAND() function calculates a random number from 0 to 1. the NORMSINV() function takes a fraction between 0 and 1 and tells you how many standard deviations you need to go above or below the mean for a cumulative Gaussian distribution to contain that fraction of the entire population.

Multiple by the standard deviation and add a mean, and you'll have random numbers drawn from a Gaussian distribution with that mean and SD. For example, use this formula to sample from a Gaussian distribution with a mean of 100 and a SD of 15:

=(NORMSINV(RAND())*15)+100

The method listed above was added to this page in April 2007. Before I thought of the easy method above, I created the macro below. I don't see any advantage to using a macro, except with old versions of Excel, and suggest you simply use to formula above.

Here  is an Excel Macro that does the job. Note that it relies on Excels RND() function to generate random numbers, and then "converts" them to Gaussian.

Function RandGauss(Mean, Sd) As Double

'from Numerical Recipes in C, second edition, page 289
'returns a random number from Gaussian distribution with mean and SD specified
Static NextRnd As Double
Static RndWaiting As Boolean
Static Randomized As Boolean

Dim fac, rsq, v1, v2, RandStd As Double

If Not (Randomized) Then
  Randomize
   Randomized = True
End If

If Not (RndWaiting) Then
  Do
    v1 = 2# * Rnd() - 1#
    v2 = 2# * Rnd() - 1#
    rsq = v1 * v1 + v2 * v2
    Loop Until rsq <= 1#
    fac = Sqr(-2# * Log(rsq) / rsq) 'natural log
    NextRnd = v1 * fac
    RndWaiting = True
    RandStd = v2 * fac
  Else
    RndWaiting = False
    RandStd = NextRnd
  End If
'RandStd has mean zero and SD=1.
RandGauss = (RandStd * Sd) + Mean

End Function

This function is based on one in Numerical Recipes in C, which owns the copyright. This should not be used in commercial software. Each pass through the calculations creates two values, both randomly chosen from a Gaussian distribution. One is returned, and one is saved to be used the next time the routine is called.