Excel Exponential Distribution

Generating random numbers from the exponential distribution in Excel should not be such a difficult task, but the lack of a direct function does it make it difficult. In this post, you will see the steps to generate random numbers from the exponential distribution in Excel.

Excel Exponential Distribution Plot

Understanding Exponential Distribution

The probably density function (PDF) of exponential distribution is:
f(x; lambda) = lambda * exp(-lambda * x)

where:

x is greater than or equal to zero

lambda is the rate parameter or 1/mean.

The cumulative distribution function (CDF) of exponential distribution is:

F(x; lambda) = 1 - exp(-lambda * x)

Percent Point Function (PPF) or Inverse of the cumulative distribution function, given by:

G(x) = - 1/lambda * logn(1- x)

We will use the PPF to generate exponential distribution random numbers.

Step 1: Generate Random Numbers from Uniform Distribution

  • The first step is to create a set of uniform random numbers between 0 and 1. To generate these random numbers, simple enter this following command in your Excel sheet cell A2:

=RAND()

  • Copy the formula down to A21, so that we have 20 random numbers from A2:A21.

Step 2: Calculate Mean of the Random Numbers

In a blank cell, say A22, calculate the mean of the numbers. Enter this formula:

=AVERAGE(A2:A21)

 

Step 2: Generate Random Numbers from Exponential Distribution

  • In cell B2, enter this formula (there is a minus sign in front of A22):

=-A22 * LN(1 - A2)

where:

LN is the natural logarithm value.

  • In the formula, make A22 absolute value by place the $ sign in front of the letter and numbers, such as $A$22.
  • Drag the formula down to B21

See this following image for an example:

Excel Exponential Distribution Formula

There you have it: you have successfully generated random numbers in Excel from the exponential distribution.

About the Author

The author of Tableau Data Visualization Cookbook and an award winning keynote speaker, Ashutosh R. Nandeshwar is one of the few analytics professionals in the higher education industry who has developed analytical solutions for all stages of the student life cycle (from recruitment to giving). He enjoys speaking about the power of data, as well as ranting about data professionals who chase after “interesting” things. He earned his PhD/MS from West Virginia University and his BEng from Nagpur University, all in industrial engineering. Currently, he is leading the data science, reporting, and prospect development efforts at the University of Southern California.

>