Article ID: 828130  View products that this article applies to. On This PageSUMMARYThis 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 informationThe 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.MORE INFORMATIONWhen 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 nonnegative integer. Syntax
Example UsageVehicles 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. Collapse this table
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 ExcelSee 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 noncumulative case POISSON(x, mu, false) uses the following formula:
Note 1 Knusel, L. "On the Accuracy of Statistical Distributions in Microsoft Excel 97", Computational Statistics and Data Analysis (26) (1998): 375377. Results in Excel 2003 and later versions of ExcelBecause Microsoft has diagnosed when overflow causes POISSON to return #NUM! and knows that POISSON is wellbehaved 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 m1 to m2, 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.
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. Collapse this table
Then, to calculate the probability of 10 or fewer arrivals, type the following formula in any blank cell:
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. ConclusionsInaccuracies 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!. PropertiesArticle ID: 828130  Last Review: September 19, 2011  Revision: 4.0
