# 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

This Excel formula generates a random value between 0.0 and 1.0. It is an Excel function, at least in name, so it needs to be followed by parentheses, but the function has no inputs so there is nothing between the parentheses.

=RAND()

This Excel formula chooses a random integer randomly from the range between (and including) the low and high values entered. Note that the result is always an integer.

=RANDBETWEEN(low, high)

Random numbers from a Gaussian distribution
This Excel formula computes a random number from a Gaussian distribution with a mean of 0.0 and a SD of 1.0.
=NORMSINV(RAND())

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

### Need to learn Prism 6?

These guided examples of common analyses will get you off to a great start! CLICK HERE >