Generating Random Variates in Excel Using Built-in Functions

Random numbers drawn from a particular probability distribution (i.e., random variates) are frequently needed in many fields, particularly for simulations. There are many algorithms for generating random variates, but I will focus on one simple technique that can be used with some built-in Excel functions. This technique is known as the Inverse Transform Method. For our purposes, we want to avoid using any VBA programming or any multi-step algorithms that might be challenging to implement in a spreadsheet, so we will be using only built-in functions. The advantage, aside from simplicity, is that you can generate as many random variates as you might need by just copying a formula.

What is a Cumulative Distribution Function (CDF)?

A cumulative distribution function (CDF) is a function that tells us the probability that a random number drawn from the probability distribution will be less than or equal to some value. More formally, the CDF is the integral of the probability density function (PDF) from negative infinity to positive infinity.

For example, think about the standard normal distribution, which has a mean of 0 and a standard deviation of 1. The probability density, which is often described as a “bell curve” and is how most people think of it, is shown in the image below:

Probability density function (PDF) of the Standard Normal Distribution — The Standard Normal Probability Density Function (PDF)

Because this distribution is symmetrical around the mean, it should be obvious that the probability of drawing a random number from this distribution that is less than 0 will be 50%. The following chart of the standard normal Cumulative Distribution Function (CDF) shows this result:

The cumulative standard normal distribution, showing the probability of drawing a number less than or equal to zero. — The Standard Normal Cumulative Distribution Function (CDF)

You can see in the chart above that you can pick a number on the x-axis and then read the probability of drawing a value less than or equal to that value from the y-axis. The dark red line shows the probability of drawing a value less than or equal to 0. I have also added a lighter red line than shows the probability of drawing a value less than or equal to 1 (about 84%).

Of course, the CDF of a different probability distribution would give different results. Excel has built-in functions to support the following probability distributions:

Distribution	Excel Function
Standard Normal	Norm.S.Dist(x, cumulative)
Normal	Norm.Dist(x, mean, standard_dev, cumulative)
Beta	Beta.Dist(x, alpha, beta, cumulative, [A], )
Binomial	Binom.Dist(number_s, trials, probability_s, cumulative)
Chi Square	ChiSq.Dist(x, deg_freedom, cumulative)
F	F.Dist(x, deg_freedom1, deg_freedom2, cumulative)
Gamma	Gamma.Dist(x, alpha, beta, cumulative)
Lognormal	LogNorm.Dist(x, mean, standard_dev, cumulative)
Student T	T.Dist(x, deg_freedom, cumulative)

Excel’s Built-In Probability Distribution Functions

There are a few others, but I am omitting them because they do not have a corresponding inverse function.

The Inverse Transform Method

The goal of this article is to demonstrate how to use some built-in functions to generate random numbers (variates) from certain probability distributions. The inverse transform method is one way to do this by inverting what we saw in the above chart. Note that there is a one-to-one correspondence between the values on the y-axis and those on the x-axis. Further, note that the values on the y-axis are probabilities, thus they are all between 0 and 1. Therefore, if we can randomly choose a probability (i.e., a value between 0 and 1) then we can find the associated x value. This x-value will then be a random variate drawn from the chosen probability distribution.

A chart of the cumulative distribution function (CDF) of the standard normal distribution showing how to generate a random variate — Generate a Random Variate from the Standard Normal Distribution

The inverse transform method works as follows:

Generate a uniformly distributed random variate (call it $u$) in the range 0 to 1.
Plug $u$ into the inverse cumulative distribution function (inverse CDF, also known as the quantile function of the distribution) of the appropriate probability distribution (e.g., the normal distribution, the gamma distribution, etc.). The resulting value (call it $x$) is a random variable drawn from the chosen probability distribution.

For step 1 we can use the Rand function. This generates a uniformly distributed random variate between 0 and 1, which is exactly what we need. Note that this function is volatile, so the random number that it generates will change every time the worksheet is recalculated.

Step 2 is typically more difficult because many cumulative distribution functions do not have a closed-form inverse (e.g., the normal distribution). However, Excel has built-in functions for the inverse of several probability distributions that we can use.

Distribution	Excel Function
Standard Normal	Norm.S.Inv(probability)
Normal	Norm.Inv(probability, mean, standard_dev)
Beta	Beta.Inv(probability, alpha, beta, [A], )
Binomial	Binom.Inv(trials, probability_s, alpha)
Chi Square	ChiSq.Inv(probability, deg_freedom)
F	F.Inv(probability, deg_freedom1, deg_freedom2)
Gamma	Gamma.Inv(probability, alpha, beta)
Lognormal	LogNorm.Inv(probability, mean, standard_dev)
Student T	T.Inv(probability, deg_freedom)

Excel’s Built-In Inverse CDF Functions

The table above shows the inverse CDF functions that we can use to generate random variates. The very simple “trick” that we will use is to insert the Rand() function for the probability argument. The only exception is with the Binom.Inv() function, where Rand() will replace the alpha argument.

Examples of Drawing Random Variates

In this section I will show several examples of generating random variates using Excel’s built-in functions for the inverse cumulative distribution functions. Once you understand the idea, you will see how easy it is. In all cases, you can generate as many random numbers as you want by simply copying the function to additional cells. There is also an example spreadsheet that contains all of these examples, including the graphics. In the spreadsheet I have generated a sample of 500 random variates from each of the distributions. The images show the CDF for each distribution and the empirical CDF for the random variates. In all cases, the fit appears to be quite good (more random variates would improve the fit).

The Standard Normal Distribution

Draw a random variate from a standard normal distribution:

=Norm.S.Inv(Rand())

Or equivalently:

=Norm.Inv(Rand(), 0, 1)

The CDF of the Standard Normal Distribution and a fit using generated random variates — Empirical and Actual CDF of the Standard Normal Distribution

The Normal Distribution

Draw a random variate from a normal distribution with a mean of 20 and a standard deviation of 5:

=Norm.Inv(Rand(), 20, 5)

The CDF of the Normal Distribution and a fit using generated random variates — Empirical and Actual CDF of the Normal Distribution

The Beta Distribution

Choose a random variate from a beta distribution with alpha = 2, beta = 0.25, lower bound of 0, and an upper bound of 1. Note that these are the default lower and upper bounds, so they may be omitted.

=BETA.INV(Rand(), 2, 0.25, 0, 1)