(Click On Image To See Enlarged View)

This is one of the following six articles on Solving Problems With Other Distributions in Excel

Poisson Distribution

Overview

The Poisson distribution is a family of discrete probability distributions. This is evidenced by the stepwise shape of the above graph of a Poisson distribution’s PDF (Probability Density Function) curve. The stepwise shape of a discrete distribution indicates that the discrete distribution can only assume discrete values and is not continuous.

The Poisson distribution has one parameter, its rate parameter λ (Lamda). The rate parameter λ equals the average number of events occurring in a given unit of time. The Poisson Distribution is used to calculate the probability of a specific number of events occurring over a unit of time if the average number of events occurring over that unit of time equals the rate parameter λ and the occurrence of the event is distributed according to the Poisson distribution.

Previous measurement must have been taken to determine the following:

1) The events occur in frequency according to the Poisson distribution

2) The average rate, which is the expected number of occurrences of that event over the given time period.

The PDF (Probability Density Function) of the Poisson distribution predicts the degree of spread around a known average rate of occurrence.

Examples of events whose frequency of occurrence over a given period of time are often distributed according to the Poisson distribution are the following:

Number of telephone calls that come over a switchboard

Number of cars arriving at a traffic light

Number of accidents at an intersection

Number of customers arriving at a sales counter

Number of insurance losses/claims filed

Number of goals in sports involving two competing teams

Number of jumps in stock price

Number of times a web server is accessed

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.

Poisson Distribution’s PDF –

Probability Density Function

The following Excel-generated graph shows the normal distribution’s PDF (Probability Density Function) for as the X value goes from 2 to 15 with λ = 10.

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. 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 Enlarged View)

Poisson Distribution’s CDF –

Cumulative Distribution

Function

The following Excel-generated graph shows the Poisson distribution’s CDF (Cumulative Distribution Function) for λ = 10 as the X value goes from 2 to 35.

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. 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 Enlarged View)

Poisson PDF Problem Solved in Excel

Calls made to a help line are Poisson-distributed and are received with an average frequency of 4.8 calls per minute. What is the probability that EXACTLY 4 calls will be received during any minute?

The problem asks to calculate the probability that the calls frequency will be EXACTLY 4 calls/minute so the Poisson’s PDF (Probability Density Function) will be used to solve this problem.

If a call comes in with an average frequency of 4.8 calls/minute, the rate parameter λ = 4.8.

The X value to be evaluated at a rate of X = 4 calls/minute

The Excel equation to solve the problem is as follows:

f(X=4;λ=4.8) = POISSON.DIST(X,λ,FALSE) = POISSON.DIST(4,4.8,FALSE) = 0.182

There is a 18.2 percent probability that EXACTLY 4 calls will come in during any minute. This agrees with the PDF graph which X = 4 corresponds with Y = 0.182 as follows:

(Click On Image To See Enlarged View)

Poisson CDF Problem Solved in Excel

Calls made to a help line are Poisson-distributed and are received with an average frequency of 4.8 calls per minute. What is the probability that UP TO 4 calls will be received during any minute?

The problem asks to calculate the probability that the calls frequency will be UP TO 4 calls/minute so the Poisson’s CDF (Cumulative Distribution Function) will be used to solve this problem.

If a call comes in with an average frequency of 4.8 calls/minute, the rate parameter λ = 4.8.

The X value to be evaluated at a rate of X = 4 calls/minute

The Excel equation to solve the problem is as follows:

f(X=4;λ=4.8) = POISSON.DIST(X,λ,TRUE) = POISSON.DIST(4,4.8,TRUE) = 0.4763

There is a 47.63 percent probability that UP TO 4 calls will come in during any minute. This agrees with the CDF graph which X = 4 corresponds with Y = 0.4763 as follows:

(Click On Image To See Enlarged View)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Technorati Tags: excel,excel 2010,excel 2013,statistics,poisson

Become an Excel Statistical Master

Excel Master Series - MBA-level statistics - Over 1,100+ Pages of Easy-To-Follow Instructions in Excel

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

Step-By-Step Optimization With Excel Solver

What's In It?

For anyone who wants to be operating at a high level with the Excel Solver quickly, this is the book for you. Step-By-Step Optimization With Excel Solver is a 200+ page .pdf e-manual of simple yet thorough explanations on how to use the Excel Solver to solve today’s most widely known optimization problems. Loaded with screen shots that are coupled with easy-to-follow instructions, this book will simplify many difficult optimization problems and make you a master of the Excel Solver almost immediately.

Here are just some of the Solver optimization problems that are solved completely with simple-to-understand instructions and screen shots in this e-manual:

• The famous “Traveling Salesman” problem using Solver’s Alldifferent constraint and the Solver’s Evolutionary method to find the shortest path to reach all customers. This also provides an advanced use of the Excel INDEX function.

• The well-known “Knapsack Problem” which shows how optimize the use of limited space while satisfying numerous other criteria.

• How to perform nonlinear regression and curve-fitting on the Solver using the Solver’s GRG Nonlinear solving method.

• How to solve the “Cutting Stock Problem” faced by many manufacturing companies who are trying to determine the optimal way to cut sheets of material to minimize waste while satisfying customer orders.

• Portfolio optimization to maximize return or minimize risk.

• Venture capital investment selection using the Solver’s Binary constraint to maximize Net Present Value of selected cash flows at year 0. Clever use of the If-Then-Else statements makes this a simple problem.

• How use Solver to minimize the total cost of purchasing and shipping goods from multiple suppliers to multiple locations.

• How to optimize the selection of different production machine to minimize cost while fulfilling an order.

• How to optimally allocate a marketing budget to generate the greatest reach and frequency or number of inbound leads at the lowest cost.

Step-By-Step Optimization With Excel Solver has complete instructions and numerous tips on every aspect of operating the Excel Solver. You’ll fully understand the reports and know exactly how to tweek all of the Solver’s settings for total custom use. This e-manual also provides lots of inside advice and guidance on setting up the model in Excel so that it will be as simple and intuitive as possible to work with.

All of the optimization problems in this book are solved step-by-step using a 6-step process that works every time. In addition to detailed screen shots and easy-to-follow explanations on how to solve every optimization problem in the book, a link is provided to download an Excel workbook that has all problems completed exactly as they are in this e-manual.

Step-By-Step Optimization With Excel Solver is exactly the e-manual you need if you want to be optimizing at an advanced level with the Excel Solver quickly.

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

Immediate, Absolute, No-Questions-Asked, Money-Back Guarantee If Not TOTALLY, 100% Satisfied. In Other Words, If Any Excel Master Series eManual That You've Purchased Here Does Not Provide Instructions That Are CRYSTAL CLEAR and EASY TO UNDERSTAND, You Get All Of Your Money Back Immediately and Keep the eManual. Guaranteed!

Meet The Author