Excel Statistical Functions: POISSON

This article describes the POISSON function in Microsoft Excel, illustrates how to use the function, and compares the results of the function for Microsoft Office Excel 2003 and later versions of excel with the results of POISSON when it is used in earlier versions of Excel.

Microsoft Excel 2004 for Macintosh information

The statistical functions in Microsoft Excel 2004 for Mac were updated by using the same algorithms that were used to update the statistical functions in Excel 2003 and later. Any information in this article that describes how a function works or how a function was modified for Excel 2003 and later also applies to Excel 2004 for Mac.

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

Notex 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 paste the entries so that the following table fills cells A1:C29 in your worksheet. 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, use one of the following methods:

• In Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
• In 2003 and earlier, 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 or a later version of Excel, 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. Knusel documented 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 and later versions of Excel

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 and later versions of Excel. 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.Step 2: Calculate unscaled probabilities for k > m:Step 3: Calculate unscaled probabilities for k < m:Step 4: Combine the unscaled results: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 paste the entries so that the following table fills cells D1:D26 in your worksheet. 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 and later versions of Excel, 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 and later versions of Excel implement an alternative plan algorithm, such as the one for POISSON, to return correct answers in cases where earlier versions of Excel return #NUM!.