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:
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
Randomized = True
If Not (RndWaiting) Then
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
RndWaiting = False
RandStd = NextRnd
'RandStd has mean zero and SD=1.
RandGauss = (RandStd * Sd) + Mean
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.
Keywords: VBA macro Normal deviate random value algorithm