## Frequently Asked Questions

# Generating random numbers with Excel

FAQ# 966 Last Modified 1-January-2009

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**

**Random numbers from a Gaussian 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.

Keywords: VBA macro Normal deviate random value algorithm