How to Plot Poisson Distribution in Excel (with Easy Steps)

This article will describe how to plot Poisson distribution in Excel based on sample data. First, we will discuss what Poisson distribution is and what it does mean, and which Excel function we use to calculate this. Then we will see how to plot the graph.

Look at the following image for a brief and quick idea.

Sample of Poisson Distribution Graph


Download Practice Workbook

Download the following sample workbook from the link below to practice along with it.


What Is Poisson Distribution?

To say very simply, the Poisson Distribution shows the probability of happening an incident for a specific number of instances in a certain period.

For example, say, a football player scores the following number of goals in 5 matches: 0, 1, 1, 3, 0. Now, what is the probability of scoring a certain number of goals in a certain number of matches? The Poisson distribution graph will give an idea of this.

The Poisson Distribution can be described by a statistical function which is as follows:

f(X,位)=位Xe-位/X!
  • Here, X is for the number of successful incidents at a given interval of time
  • is the mean number of successes during the same interval of time.

To understand in detail, read the whole article, we have discussed an example for your better understanding.


Introduction to POISSON.DIST Function in Excel

MS Excel provides POISSON.DIST function from Excel 2007 version. The objective of this function is simply to return Poisson Distribution in Excel.

POISSON.DIST Function in Excel

It has the following arguments. All of them are of the required type.

  • X refers to the number of expected incidents.
  • Mean refers to the expected numeric value.
  • Cumulative is a logical value that refers to the type returned probability distribution. If it is TRUE, the function returns the cumulative probability output for zero to X inclusive. If it is FALSE, it returns the Poisson probability for exactly x.

Steps to Plot Poisson Distribution of a Sample Data in Excel

Let鈥檚 get introduced to our sample data first. You have to gather sample data like this first off.

The following is data of a Toll Plaza. It describes the number of bus arrival incidents at 15 minutes of intervals. In this example, we will find the probability of arrival of a certain number of buses at a 1-hour interval.

Data of Bus Arrival in a Toll Plaza


馃搶 Step 1: Calculate Mean, 位

  • The first thing is to calculate the mean, 位 of this data. It鈥檚 very simple. Just divide the number of occurrences by the number of cases.
  • In this particular case, we have calculated 位 with the following formula in cell E5.
=SUM(B5:B101)/COUNT(B5:B101)

Calculate Mean of Poisson Distribution Formula

  • Here, SUM(B5:B101) returns the number of buses that arrive at the toll plaza.
  • COUNT(B5:B101) is the number of case-count.

馃搶 Step 2: Calculate Poisson Distribution

  • Now, arrange a table like the following.

What are we going to calculate here? Suppose you are thinking that what is the probability that 5 buses will arrive at the toll plaza during a 1-hour interval. We will calculate this now.

  • To do that, insert the following formula in cell C5, and drag the fill handle icon to the last cell of this order.
=POISSON.DIST(B5,'Source Data'!$E$5,FALSE)

Results to Plot Poisson Distribution in Excel

Here, B5 is the number of incidents X, 鈥楽ource Data鈥!$E$5 refers to the mean 位, and FALSE is for non-cumulative Poisson Distribution. If we would do this calculation in the same sheet as the source data, the formula would be =POISSON.DIST(B5,$E$5,FALSE). Not a big issue, right? Don鈥檛 forget to use Absolute Reference for mean. Otherwise, you will not get the correct result.


馃搶 Step 3: Plot Poisson Distribution Results

We are almost done!

  • Now, select the f(X,位) column and go to the Insert tab. Then from the Chart group, click on the Insert Line or Area Chart drop-down menu, and select the first icon of the 2-D Area section.

Roadmap to Plot Poisson Distribution in Excel

The following graph will appear.

Excel Poisson Distribution Plot

  • We have completed our job here. But we can add some formatting to get a better interpretation and to have an eye-soothing graph. After applying the percentage format for Poisson Distribution results, style 2 for the chart, and changing the chart title, we have the following result.


Quick Notes

  • In the Poisson Distribution formula, you must use absolute reference for the mean, or hard code the mean in the formula.
  • All the inputs must be numeric, otherwise #VALUE! error occurs.
  • X and 位 must not be less than zero, otherwise, you will get #NUM! Errors.
  • If X is not a whole number, Excel will truncate it to a whole number.
  • If you choose TRUE instead of FALSE in the formula =POISSON.DIST(B5,鈥橲ource Data鈥!$E$5,FALSE), you will get cumulative results that indicate the probability of 0 to X. But choosing FALSE will return the probability of exactly X.

Conclusion

So, in this article, we have discussed how you can plot Poisson Distribution in Excel. If you have found this write-up useful, please let us know in the comment box. Also, don鈥檛 hesitate to ask if you have any queries. For more such articles, please visit our blog ExcelDemy.

Masum Mahdy

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo