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
Where do you meet this distribution?
- The lengths of the inter-arrival times in a homogeneous Poisson process
- Nuclear physics : The time until a radioactive particle decays
- Statistical mechanics : Molecular distribution in uniform gravitational field
- Risk management : The time until default in reduced form credit risk modeling
Shape of Distribution
Basic Properties
- A parameter
is required.
This parameter is Mean of the distribution.
- Continuous distribution defined on semi-infinite range
- This distribution is always asymmetric.
Probability
- Cumulative distribution function
- Probability density function
- How to compute these on Excel.
1 2 3 4 5 6 AB DataDescription 0.5 Value for which you want the distribution 8 Value of parameter Beta FormulaDescription [Result] =1-EXP[-A2/A3] Cumulative distribution function for the terms above =EXP[-A2/A3]/A3 Probability density function for the terms above
Quantile
- Inverse function of cumulative distribution function
- How to compute this on Excel.
1 2 3 4 5 AB DataDescription 0.5 Probability associated with the distribution 1.7 Value of parameter Beta FormulaDescription [Result] =-A3*LN[1-A2] Inverse of the cumulative distribution function for the terms above
Characteristics
Mean – Where is the “center” of the distribution? [Definition]
- Mean of the distribution is given as .
Standard Deviation – How wide does the distribution spread? [Definition]
- Standard deviation of the distribution is given as .
Skewness – Which side is the distribution distorted into? [Definition]
- Skewness is .
Kurtosis – Sharp or Dull, consequently Fat Tail or Thin Tail [Definition]
- Kurtosis is .
Random Numbers
- Random number x is generated by inverse function method, which is for uniform random U,
- How to generate random numbers on Excel.
1 2 3 4
AB DataDescription 0.5 Value of parameter Beta FormulaDescription [Result] =-A2*LN[1-NTRAND[100]] 100 exponential deviates based on Mersenne-Twister algorithm for which the parameters above Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A4:A103 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER.
NtRand Functions
Not supported yet
Reference
- Wolfram Mathworld – Exponential distribution
- Wikipedia – Exponential distribution
- Statistics Online Computational Resource