## Wednesday, May 28, 2014

### Solving Exponential Distribution Problems in Excel 2010 and Excel 2013 (Click On Image To See a Larger Version)

# Overview of the Exponential Distribution

The exponential distribution is used to calculate the probability of occurrence of an event that is the result of a continuous decaying or declining process such as the time until a radioactive particle decays. The exponential distribution is also used to model the waiting times between rare events.

The exponential distribution calculates the probability of a specific interval, usually of time, to the first event of a Poisson process. Examples of arrival or waiting times in Poisson processes that could be analyzed with the exponential distribution are as follows:

Time between telephone calls that come over a switchboard

Time between accidents

Time between traffic arrivals

Time between defects

The exponential distribution is closely related to the gamma distribution. The exponential distribution calculates the probability of a specific waiting time until the 1st Poisson event occurs. The gamma distribution calculates the probability of a specific waiting time until the kth event Poisson occurs.

The exponential distribution is not appropriate for predicting failure rates of devices or lifetimes of organisms because a disproportionately high number of failures occur in the very young and the very old. In these cases, the distribution curve would not be a smooth exponential curve as described by the exponential distribution. The Weibull distribution is commonly used to model time-to-failure for devices.

The exponential distribution predicts time between Poisson events as follows:

Probability of length of time t between Poisson events = f(t) = λe-λt

λ (Lamda) is the rate parameter. If, for example, there are 3 events per hour on average, then λ = 3 if time is expressed in units of hours. The exponential distribution calculates the probability of the event occurring at time t given that rate parameter λ = 3 and the occurrence of the event is Poisson-distributed. The average time between events is 1/λ which is 1/3 hours between events.

## Exponential Distribution’s PDF – Probability Density Function

The following Excel-generated graph shows the exponential distribution’s PDF (Probability Density Function) for as the X value (time between Poisson events) goes from 0 to 2.9 with Rate parameter λ = 1.5. This Rate parameter indicates that 1.5 Poisson-distributed events occur on average in each time period.

The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. This is shown in the Excel-generated graph below. The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value. (Click On Image To See a Larger Version)

## Exponential Distribution’s CDF – Cumulative Distribution Function

The following Excel-generated graph shows the Exponential distribution’s CDF (Cumulative Distribution Function) for λ =1.5 Poisson-distributed occurrences per time period on average as the X value (the time between events in a Poisson process) goes from 0 to 2.9.

The CDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be up to that X value if the population of data values from which the sample was taken is distributed according the stated distribution. This is shown in the Excel-generated graph below. The PDF value of a statistical distribution (the Y value) at a specific X value equals the probability that the value of a random sample will be equal to that X value. (Click On Image To See a Larger Version)

### Characteristics of Poisson-Distributed Events

The Poisson distribution is used for situations that involve counting events over identical intervals of time or objects over identical intervals of volume. If each count is independent of the others, the probability of an event occurring in any of the intervals is constant, and the average count is known, the Poisson distribution can be used to calculate the probability of a specific number of events occurring in an interval.

The Poisson distribution has only one parameter: the rate parameter λ. The rate parameter λ (Lamda) equals the average number of occurrences over the intervals. λ also equals the variance in the number of occurrences over the intervals. One check of whether data are Poisson-distributed is whether the mean number of occurrences equals the variance n the number of occurrences over the intervals.

The Poisson distribution is based upon the following four assumptions:

1) The probability of an event occurring remains constant in all intervals.

2) All events are independent of each other and do not overlap.

3) The probability of observing a single event over a small interval is approximately proportional to the size of that interval.

4) The mean number of occurrences per interval (λ) and the variance in the number of occurrence per interval are approximately the same.

## Exponential CDF Problem Solved in Excel

Customer arrivals at a store’s service desk are Poisson-distributed. On average one customer appears at the customer service desk every 10 minutes. Units of time in which the counts are made are minutes. What is the probability that the wait between two customer arrivals will be UP TO 3 minutes?

The problem asks to calculate the probability that the wait time will be UP TO 3 minutes so the exponential’s CDF (Cumulative Distribution Function) will be used to solve this problem.

If a customer arrives at the service desk on average every 10 minutes, the rate of customer arrivals is 1 customer / 10 minutes or 1/10. The rate parameter λ = 1/10 = 0.10.

The X value to be evaluated is X = 3 minutes

The Excel equation to solve the problem is as follows:

F(X=3;λ=0.1) = EXPON.DIST(X,λ,TRUE) = EXPON.DIST(3,0.1,TRUE) = 0.2592

There is a 25.92 percent probability that the wait between two customer arrivals will be UP TO 3 minutes. This agrees with the CDF graph which X = 3 corresponds with Y = 0.2592 as follows in this Excel-generated graph: (Click On Image To See a Larger Version)

The exponential distribution equals the gamma distribution when gamma distribution parameters are set as follows:

k = 1

θ = 1/λ

In this case the following is true:

EXPON.DIST(X, λ, FALSE) = GAMMA.DIST(X, 1, 1/λ, FALSE)

and

EXPON.DIST(X, λ, TRUE) = GAMMA.DIST(X, 1, 1/λ, TRUE)

The gamma distribution calculates the probability of wait time for the kth Poisson event. Setting k to 1 configures the gamma distribution to calculate the probability of wait time for the first Poisson event if the average rate is λ. The exponential distribution also calculates the probability of wait time to the first Poisson event when the average rate time is λ. The following two Excel-generated graphs confirm this: (Click On Image To See a Larger Version) (Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel