You are currently offline, waiting for your internet to reconnect

`POISSON(x, mu, cumulative)`

To illustrate the POISSON function, create a blank Excel worksheet, copy the following table, select cell

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 |

- 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**.

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.

For the non-cumulative case POISSON(

`EXP(-x)*(mu^x)/FACT(x)`

Note 1 Knusel, L. "On the Accuracy of Statistical Distributions in Microsoft Excel 97", Computational Statistics and Data Analysis (26) (1998): 375-377.

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

`TotalUnscaledProbability = TotalUnscaledProbability + 1;If (m == x) then UnscaledResult = UnscaledResult + 1;If (cumulative && m < x) then UnscaledResult = UnscaledResult + 1;`

`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;`

`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;`

`Return UnscaledResult/TotalUnscaledProbability;`

Copy the table below, select cell

=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) |

Then, to calculate the probability of 10 or fewer arrivals, type the following formula in any blank cell:

`=SUM(D3:D13)/D26`

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

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!.

XL2007

Properties

Article ID: 828130 - Last Review: 09/19/2011 00:03:00 - Revision: 4.0

- Microsoft Office Excel 2007
- Microsoft Excel 2004 for Mac

- kbexpertisebeginner kbinfo KB828130

microsoft.com/ms.js" '="">