Cart

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

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