Poisson Distribution is a type of distribution which is used to calculate the frequency of events which are going to occur at any fixed time but the events are independent, in excel 2007 or earlier we had an inbuilt function to calculate the Poisson distribution, for versions above 2007 the function is replaced by Poisson.DIst function.
Poisson Distribution in Excel
Poisson distribution is used in analyzing the variance against the average occurrence of that event in each time frame. We can conduct this statistical analysis in excel with the help of a built-in function of excel i.e. Poisson Distribution in Excel (“POISSON.DIST” function).
Let’s take a look at the syntax of the POISSON.DIST function.
X: This is the number of events. This should be >=0.
Mean: The expected number of events. This is also should be >=0.
Cumulative: This will decide the type of distribution to be calculated. We have two options here TRUE or FALSE.
- TRUE indicates the probability of a number of events happening between zero and x.
- FALSE indicates the probability of the number of events happening exactly the same as the x.
Examples of Poisson Distribution in Excel
There can be a lot of examples regarding the Poisson distribution in excel.
As a car rental company owner your average weekend car rental customers are 500. You are expecting 520 customers in the coming weekend.
You want to know the probability percentage of this event occurring in the coming week.
Here x is 520 and the mean is 500. Enter these details in excel.
Open POISSON.DIST function in any of the cell.
Select the x argument as the B1 cell.
Select Mean argument as B2 cell.
We are looking at the “cumulative distribution function” so select TRUE as the option.
So, we got the result as 0.82070. Now in the below cell apply the formula as 1 – B5.
So, the probability of increasing car rental customers from 500 to 520 in the coming week is about 17.93%.
In the production of 1000 units of automobile products, the average percentage of defects products is about 6%. Similarly in a sample of 5000 products what is the probability of having 55 defect products?
First calculate the number of defect products in 1000 units. i.e. λ = np. λ = 1000 * 0.06.
So, the total number of defect products in 1000 units is 60 units. Now we got the total defects number (x). So x = 60.
Now to decrease the defect products from 60 to 55 we need to find the excel Poisson Distribution percentage.
So, MEAN = 55, x = 60.
The above formula will give us the Poisson distribution value. In the below cell apply the formula 1 – Poisson distribution in excel.
So, the probability of reducing defect items from 60 to 55 is about 23%.
Things to Remember
- We will get the number error of #NUM! is the supplied x & Mean values are less than zero.
- We will get #VALUE! If the arguments are non-numeric.
- If the supplied numbers are decimal or fraction, then excel automatically rounded to the nearest integer number.
This has been a guide to Poisson Distribution in Excel. Here we discuss how to calculate Poisson distribution in excel using POISSON.DIST function along with examples and downloadable excel template. You may also look at these useful functions in excel –