MORE INFORMATION
When cumulative = TRUE, the function
POISSON(
x,
mu,
cumulative) returns the probability that a POISSON random variable with mean
mu takes on a value less than or equal to
x. When cumulative = FALSE, POISSON returns the
probability that such a random variable takes on a value exactly equal to
x. The POISSON distribution is frequently used to
model the number of occurrences of certain events such as the number of
customers who arrive in a queuing facility or the number of proofreading errors
in an article. Because the POISSON distribution is used to count in this
manner,
x must be a non-negative integer.
Syntax
POISSON(x, mu, cumulative)
is a non-negative integer,
mu is a positive number but not necessarily an
integer, and cumulative is a logical variable that takes on the values TRUE or
FALSE.
Example Usage
Vehicles arrive at an intersection at a rate of 10 per minute. A
traffic light cycle lasts 45 seconds. What is the distribution of the number of
vehicles that arrive per cycle? The average number of such vehicles is 10 *
0.75 = 7.5 because 10 vehicles arrive per minute on average, and 45 seconds is
0.75 minutes. The actual number of vehicles that arrive follows a Poisson
distribution with mean 7.5.
To illustrate the POISSON function, create
a blank Excel worksheet, copy the following table, select cell
A1 in your blank Excel worksheet, and then click
Paste on the
Edit menu so that the entries in
the following table fill cells A1:C29 in your worksheet.
| mean, mu | 7.5 | |
| arrivals, x | P(exactly x arrivals) | P(x or
fewer arrivals) |
| 0 | =POISSON(A3,$B$1,FALSE) | =POISSON(A3,$B$1,TRUE) |
| 1 | =POISSON(A4,$B$1,FALSE) | =POISSON(A4,$B$1,TRUE) |
| 2 | =POISSON(A5,$B$1,FALSE) | =POISSON(A5,$B$1,TRUE) |
| 3 | =POISSON(A6,$B$1,FALSE) | =POISSON(A6,$B$1,TRUE) |
| 4 | =POISSON(A7,$B$1,FALSE) | =POISSON(A7,$B$1,TRUE) |
| 5 | =POISSON(A8,$B$1,FALSE) | =POISSON(A8,$B$1,TRUE) |
| 6 | =POISSON(A9,$B$1,FALSE) | =POISSON(A9,$B$1,TRUE) |
| 7 | =POISSON(A10,$B$1,FALSE) | =POISSON(A10,$B$1,TRUE) |
| 8 | =POISSON(A11,$B$1,FALSE) | =POISSON(A11,$B$1,TRUE) |
| 9 | =POISSON(A12,$B$1,FALSE) | =POISSON(A12,$B$1,TRUE) |
| 10 | =POISSON(A13,$B$1,FALSE) | =POISSON(A13,$B$1,TRUE) |
| 11 | =POISSON(A14,$B$1,FALSE) | =POISSON(A14,$B$1,TRUE) |
| 12 | =POISSON(A15,$B$1,FALSE) | =POISSON(A15,$B$1,TRUE) |
| 13 | =POISSON(A16,$B$1,FALSE) | =POISSON(A16,$B$1,TRUE) |
| 14 | =POISSON(A17,$B$1,FALSE) | =POISSON(A17,$B$1,TRUE) |
| 15 | =POISSON(A18,$B$1,FALSE) | =POISSON(A18,$B$1,TRUE) |
| 16 | =POISSON(A19,$B$1,FALSE) | =POISSON(A19,$B$1,TRUE) |
| 17 | =POISSON(A20,$B$1,FALSE) | =POISSON(A20,$B$1,TRUE) |
| 18 | =POISSON(A21,$B$1,FALSE) | =POISSON(A21,$B$1,TRUE) |
| 19 | =POISSON(A22,$B$1,FALSE) | =POISSON(A22,$B$1,TRUE) |
| 20 | =POISSON(A23,$B$1,FALSE) | =POISSON(A23,$B$1,TRUE) |
| 21 | =POISSON(A24,$B$1,FALSE) | =POISSON(A24,$B$1,TRUE) |
| 22 | =POISSON(A25,$B$1,FALSE) | =POISSON(A25,$B$1,TRUE) |
| | |
| =POISSON(1400,1500,TRUE) | 0.004744099 | |
| =POISSON(133,200,TRUE) | 0.00000029439 | |
| =POISSON(134,200,TRUE) | 0.000000445617 | |
Note After you paste this table into your new Excel worksheet, click
Paste Options, and then click
Match
Destination Formatting. With the pasted range still selected, point to
Column on the
Format menu, and then click
AutoFit Selection. You may want to format cells B3:C25 for
consistent readability, such as Numbers with 7 decimal places. You may also
want to format cells A27:B29 (such as Scientific with 5 decimal
places).
Cells B3:B10 show increasing probabilities as the number of
arrivals increases, but approaches the mean and remains below the mean, 7.5.
Cells B11:B25 show decreasing probabilities as the number of arrivals
increases, remains higher than the mean, 7.5, and becomes further from the
mean. Cells C3:C25 show cumulative probabilities.
The most likely
modal number of arrivals is 7. The modal value always equals the mean if the
mean is an integer. If the mean is not an integer, as in this example, the
modal value is either the closest integer just lower than the mean or the
closest integer just higher than the mean.
Cells A27:B29 show 3 calls
to POISSON in column A and the correct results in column B. If you are using
Excel 2003, both columns agree. If you are using an earlier version of Excel,
the entries agree except in row 29 where A29 shows #NUM!. This exemplifies
errors in earlier versions of Excel.
Results in Earlier Versions of Excel
See Note 1. Knuseldocumented instances where POISSON does not
return a numeric answer and yields #NUM! instead because of a numeric overflow.
When POISSON returns numeric answers, they are correct.
For the
non-cumulative case POISSON(
x,
mu, false) uses the following formula:
Overflow occurs when
mu^
x is too large. This
does not occur if
mu^
x
< 10^290 (or equivalently
x*LOG10(
mu) < 290).
FACT(
x) also must not overflow.
x <= 170 guarantees this. However, earlier
versions of Excel do not look for these conditions.
Note 1 Knusel, L. "On the Accuracy of Statistical Distributions in
Microsoft Excel 97",
Computational Statistics and Data Analysis (26) (1998): 375-377.
Results in Excel 2003
Because Microsoft has diagnosed when overflow causes POISSON to
return #NUM! and knows that POISSON is well-behaved when overflow does not
occur, we have added a conditional algorithm in Excel 2003. The algorithm
uses POISSON code from earlier versions of Excel, the computational formula
mentioned earlier in this article, when
x*LOG10(
mu) < 290 and
x <= 170. When
x*LOG10(
mu) >= 290 or
x > 170, Excel implements an alternative plan
described later in this article. The alternative plan calculates an unscaled
sum of probabilities of each possible observed value. This unscaled sum of
probabilities is used later for scaling purposes. The algorithm also calculates
an unscaled value of the probability that you want POISSON to return. Finally,
it uses the scaling factor to return a correct POISSON value. The algorithm
takes advantage of the fact that the ratio of successive terms of the form
EXP(-
x)*(
mu^
x)/
x!
has a simple form. The algorithm works as detailed in the pseudocode that is in
the following steps. This approach is similar to the method used for the
BINOMDIST, CRITBINOM, HYPGEOMDIST, and NEGBINOMDIST functions.
Step 0: Initialization. Initialize TotalUnscaledProbability and
UnscaledResult to 0. Initialize the constant EssentiallyZero to a very small
number, for example 10^(-12).
Step 1: Round
mu down to the nearest whole
number,
m. The most likely number of arrivals is
either
m or
m+1.
EXP(-
x)*(
mu^
x)/
x!
decreases as
k decreases from
m to
m-1 to
m-2, and so on. Also,
EXP(-
x)*(
mu^
x)/
x!
decreases as
k increases from
m+1 to
m+2 to
m+3, and so on.
TotalUnscaledProbability = TotalUnscaledProbability + 1;
If (m == x) then UnscaledResult = UnscaledResult + 1;
If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;
: Calculate unscaled probabilities for
k >
m:
PreviousValue = 1;
Done = FALSE;
k = m + 1;
While (not Done && k <= n)
{
CurrentValue = PreviousValue * mu / k;
TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
If (cumulative && k < x) then UnscaledResult = UnscaledResult +
CurrentValue;
If (CurrentValue <= EssentiallyZero) then Done = TRUE;
PreviousValue = CurrentValue;
k = k+1;
}
end While;
: Calculate unscaled probabilities for
k <
m:
PreviousValue = 1;
Done = FALSE;
k = m - 1;
While (not Done && k >= 0)
{
CurrentValue = PreviousValue * k+1 / mu;
TotalUnscaledProbability = TotalUnscaledProbability + CurrentValue;
If (k == x) then UnscaledResult = UnscaledResult + CurrentValue;
If (cumulative && k < x) then UnscaledResult = UnscaledResult +
CurrentValue;
If (CurrentValue <= EssentiallyZero) then Done = TRUE;
PreviousValue = CurrentValue;
k = k-1;
}
end While;
: Combine the unscaled results:
Return UnscaledResult/TotalUnscaledProbability;
Although this method is used only for
x*LOG10(
mu) >= 290 or
x > 170, you can use the following additions to
the Excel worksheet to help you hand-execute the algorithm when you calculate
POISSON(10, 7.5, TRUE) (the chance of 10 or fewer arrivals when the average
number of arrivals is 7.5 and the actual observed number of arrivals follows a
POISSON distribution).
Copy the table below, select cell
D1 in the Excel worksheet that you created earlier, and then
click
Paste on the
Edit menu so that the
entries in the following table fill cells D1:D26 in your worksheet.
|
|
| =D4*(A3+1)/$B$1 |
| =D5*(A4+1)/$B$1 |
| =D6*(A5+1)/$B$1 |
| =D7*(A6+1)/$B$1 |
| =D8*(A7+1)/$B$1 |
| =D9*(A8+1)/$B$1 |
| =D10*(A9+1)/$B$1 |
| 1 |
| =D10*$B$1/A11 |
| =D11*$B$1/A12 |
| =D12*$B$1/A13 |
| =D13*$B$1/A14 |
| =D14*$B$1/A15 |
| =D15*$B$1/A16 |
| =D16*$B$1/A17 |
| =D17*$B$1/A18 |
| =D18*$B$1/A19 |
| =D19*$B$1/A20 |
| =D20*$B$1/A21 |
| =D21*$B$1/A22 |
| =D22*$B$1/A23 |
| =D23*$B$1/A24 |
| =D24*$B$1/A25 |
| =SUM(D3:D25) |
Column D then contains the unscaled probabilities. The 1 in
cell D10 is the result of Step 1 of the algorithm. Entries in cells D11, D12,
..., D25 are calculated in that order in Step 2, and entries in cells D9, D8,
..., and D3 are calculated in that order in Step 3. The sum of all the
unscaled probabilities appears in D26.
Then, to calculate the
probability of 10 or fewer arrivals, type the following formula in any blank
cell:
There may be a roundoff error when you compare this result with
cell C13. This behavior occurs because the table does not extend to many more
rows until entries in column D become much smaller. Therefore, the sum in cell
D26 is an underestimate of the true sum of all the unscaled probabilities,
including those that the table does not include.
In this example,
EssentiallyZero does not stop steps 2 or 3. But assume that you want to
evaluate POISSON(550, 600, TRUE). In the example, this answers the question,
"What is the chance of 550 or fewer arrivals in an hour?" because the average
number of arrivals in an hour is 600 when the average number of arrivals per
minute is 10. A POISSON random variable with
mu =
600 has a distribution that is approximated by the Normal with mean 600 and
standard deviation SQRT(600) = 24.5. Then 845 is 10 standard deviations higher
than the mean and 355 is 10 standard deviations lower than the mean. Depending
on your setting of EssentiallyZero, EssentiallyZero may stop step 2 before
k reaches 845 and may stop step 3 before
k reaches 355.
Conclusions
Inaccuracies in earlier versions of Excel occur only when
mu and
x are large enough
that
x*LOG10(
mu) >=
290 or FACT(
x) overflows because
x > 170. In such cases, POISSON returns #NUM! in
earlier versions of Excel because
mu^
x overflows. To
correct this behavior in Excel 2003, we implemented the alternative
algorithm described earlier in this article when such an overflow would
otherwise occur.
In earlier versions of Excel, the BINOMDIST,
CRITBINOM, HYPGEOMDIST, and NEGBINOMDIST functions behave similarly. In every
case, these functions also either return correct numeric results or #NUM! or
#DIV/0!. Again, problems occur because of overflow or underflow. It is easy
to identify the conditions where these problems occur, and Excel 2003
implements an alternative plan algorithm, such as the one for POISSON, to
return correct answers in cases where earlier versions of Excel return
#NUM!. For more
information about POISSON, click
Microsoft Excel Help on the
Help menu, type
poisson in the
Search for box in the Assistance pane, and then click
Start searching to view the topic.