3.4 Poisson Probability using Excel

Last updated
Save as PDF

Page ID: 22445

\( \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}}\)

Open an Excel Spreadsheet.

How to use Excel to compute probabilities, mean, and standard deviation

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 vehicles 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 two hours is ten cars. What is the probability that 13 cars enter the intersection? The probability statement is P(X = 13). To follow along, open an Excel spreadsheet.

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

Enter Mean in cell A1, and then 10 in cell B1.
Enter Parameter in cell A2, and then =1/B1 in cell B2. (1 represents two hours)
Enter P(X=13) in cell A3, and then =poisson.dist(13, B1, False) in cell B3.
Enter Variance in cell A4, and then =B1 in cell B4.
Enter Standard Deviation in cell A5, and then = sqrt(B4) in cell B5.

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

The probability that at least 13 cars entered the intersection [P(X > 13)] during the two-hour period is 1 - P(X < 12). You can find the P(X < 12) by first entering P(X <= 12) in cell A6, then =poisson.dist(12, B1, True) in cell B6. in cell C18, = 1 - 0.7916 = 0.2084.

If the period changes, then you must adjust the mean for the period. For example, if we are considering the probability for a half an hour period. Determine the number of half-hour periods within 2 hours.

**2 hour period**
1/2 hour	1/2 hour	1/2 hour	1/2 hour

There are four 1/2 hour periods in the two hours. To find the probability that four cars entered the intersection in 1/2 hour period, enter =poisson.dist(4, B1/4, false) or 0.1336.