3.3 Poisson Distribution using Excel Spreadsheet Provided

Last updated
Save as PDF

Page ID: 22444

\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

If you cannot see the Excel Spreadsheet below or want to download the Excel Spreadsheet provided, click here.

How to use the Excel Spreadsheet provided

Suppose you are standing at an intersection of Blackstone and Shaw; you want to count the number of white cars that enter the intersection during lunch 11:00 AM - 1:00 PM on Friday. You do not know how many cars you will count, but you know it is between 0 and an unknown number. This probability distribution function is called a Poisson distribution.

The mean number of white cars entering the intersection during a two-hour period is ten cars. What is the probability that 13 cars enter the intersection? The probability statement is P(X = 13). To follow along, download the Excel spreadsheet by clicking on the icon in the Excel spreadsheet below (download icon Excel Download button ).

To find the P(X = 13), we would do the following.

Enter 10 in cell B1.
Enter 1 in cell B2. (1 represents a two hour period)
Enter 0 through 13 in a separate cell starting from A6.

The probability that 13 cars entered the intersection [P(X=13)] during the two-hour period is 0.0729 (rounded to four decimal places). You can find the value in cell B19.

The probability that at least 13 cars entered the intersection [P(X > 13)] during the two-hour period is 1 - P(X < 12) = 1 - 0.7916 = 0.2084. You can find the P(X < 12) in cell C18.

The probability that at most nine cars entered through the intersection [P(X < 9)] during the two-hour period is 0.4579. You can find the probability in cell C15.

The Mean is in cell F1, 10.

The Variance is in cell F2, 10.

The Standard deviation is in cell F3, 3.16.

Excel Spreadsheet

Enter the values and see if you can match the outcomes.

Example \(\PageIndex{1}\)

Now suppose, they want to know how many white cars entered in an one-hour period (11:00 AM - 12:00 PM). We can change the Time and space variable to perform the calculations above for a one-hour period.

Solution

Enter 0.5 in cell B2; then compute the probabilities the same as above.

The probability that 13 cars entered the intersection [P(X=13)] during the two-hour period is 0.0013 (rounded to four decimal places). You can find the value in cell B19.

The probability that at least 13 cars entered the intersection [P(X > 13)] during the two-hour period is 1 - P(X < 12) = 1 - 0.9980 = 0.0020. You can find the P(X < 12) in cell C18.

The probability that at most nine cars entered through the intersection [P(X < 9)] during the two-hour period is 0.9682. You can find probability in cell C15.

The Mean is in cell F1, 5.

The Variance is in cell F2, 5.

The Standard deviation is in cell F3, 2.24.

Example \(\PageIndex{1}\)

Now suppose, you want to know the probability that 26 cars in an one-hour period (5:00 PM - 6:00 PM) if the mean number number of white cars through the intersection is 30 cars.

Solution

First, enter 30 in cell B1. Second enter 1 in cell B2. If the value in cell A6 starts at 0, the end value is less than 26. To find the answer, enter 26 in cell A6. The answer is in cell B6, entered the intersection [P(X=26)] during the one-hour period, 0.0590. Since you do not have 26 values we will enter 26 in cell A6.