Generate exponential random variable excel

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.

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:

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

Excel And Statistics Books You May Be Interested In:

No products found.

Last update on 2022-05-05 / Affiliate links / Images from Amazon Product Advertising API

Basic Concepts

The exponential distribution can be used to determine the probability that it will take a given number of trials to arrive at the first success in a Poisson distribution; i.e. it describes the inter-arrival times in a Poisson process. It is the continuous counterpart to the geometric distribution, and it too is memoryless.

Definition 1: The exponential distribution has the probability density function (pdf) given by

f(x) = λe-λx

for x ≥ 0. Lambda is called the rate parameter and λ > 0.

The cumulative distribution function (cdf) is

F(x) = 1 – e-λx

The inverse cumulative distribution function is

F-1(p) = – ln(1–p)/λ

Worksheet Functions

Excel Function: Excel provides the following function for the exponential distribution:

EXPON.DIST(x, λ, cum) = the pdf of the exponential function f(x) when cum = FALSE and the corresponding cumulative distribution function F(x) when cum = TRUE.

This function is not available in versions of Excel prior to Excel 2010. Instead, these versions of Excel use the equivalent function EXPONDIST.

Observation: The exponential distribution is equivalent to the gamma distribution with α = 1 and β = 1/λ. Thus, EXPON.DIST(x, λ, cum) = GAMMA.DIST(x, 1, 1/λ, cum).

There is no EXPON.INV(p, λ) function in Excel, but GAMMA.INV(p, 1, 1/λ) or –LN(1–p)/λ or the following Real Statistics function can be used instead.

Real Statistics Function: The Real Statistics Resource Pack supplies the following function.

EXPON_INV(p, λ) = the inverse of the exponential distribution at p

Graph

Figure 1 shows a graph of the pdf of the exponential distribution for λ = 1, 2 and 3.

Figure 1 – Pdf of exponential distribution

Properties

Key statistical properties are:

• Mean = 1 / λ
• Median = ln 2/λ
• Mode = 0
• Range = [0, ∞)
• Variance = 1 / λ2
• Skewness = 2
• Kurtosis = 6

If λ is a constant representing the average number of random events that occur in a fixed time interval, then the probability that the first such event will occur in less than x time is given by the cumulative exponential distribution function F(x).

Property 1: An exponential distribution is memoryless

Property 2: If x has a Poisson distribution with mean λ, then the time between events follows an exponential distribution with mean 1/ λ.

Click here for the proofs of these two properties.

Examples

Click here for examples based on the exponential distribution.

Reference

Wikipedia (2012) Exponential distribution
https://en.wikipedia.org/wiki/Exponential_distribution