**Part 1: Introduce**

πΉ **Definition**: The `POISSON.DIST`

function in Microsoft Excel calculates the Poisson distribution, which measures how often an event is likely to occur within a specified period.

πΉ **Purpose**: It’s used to predict the probability of certain events when you know the average number of times the event has occurred. It’s beneficial for rare events in large datasets.

πΉ **Syntax & Arguments**:

`POISSON.DIST(x, mean, cumulative) `

πΉ **Explain the Arguments in the function**:

`x`

: The actual number of events.`mean`

: The average number of times the event occurs over a specified period.`cumulative`

: A logical value; TRUE returns the cumulative distribution function; FALSE returns the probability mass function.

πΉ **Return value**: This function returns the Poisson distribution probability.

πΉ **Remarks**: The `POISSON.DIST`

function can be used for rare events, given a large sample size. Ensure that the mean is a positive number.

**Part 2: Examples**

π **Example 1**:

β’ **Purpose of example**: Determine the probability of receiving exactly 3 customer complaints daily when the average number of complaints is 2.

β’ **Data tables and formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | x | Mean | Cumulative | Result |

2 | 3 | 2 | FALSE | =POISSON.DIST(A2,B2,C2) |

3 | 0.180 |

β’ **Explanation**: Given an average of 2 complaints per day, the probability of receiving exactly 3 complaints on a particular day is 18.0%.

π **Example 2**:

β’ **Purpose of example**: Determine the cumulative probability of receiving up to 4 faulty products in a shipment when the average number of defective products is 3.

β’ **Data tables and formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | x | Mean | Cumulative | Result |

2 | 4 | 3 | TRUE | =POISSON.DIST(A2,B2,C2) |

3 | 0.647 |

β’ **Explanation**: Given an average of 3 faulty products per shipment, the cumulative probability of receiving up to 4 faulty products is 64.7%.

π **Example 3**:

β’ **Purpose of example**: Determine the probability of a website receiving precisely 10 hits in an hour when the average number of hits per hour is 8.

β’ **Data tables and formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | x | Mean | Cumulative | Result |

2 | 10 | 8 | FALSE | =POISSON.DIST(A2,B2,C2) |

3 | 0.112 |

β’ **Explanation**: Given an average of 8 website hits per hour, the probability of receiving exactly 10 hits per hour is 11.2%.

π **Example 4**:

β’ **Purpose of example**: Determine the cumulative probability of a call center receiving up to 5 calls in 10 minutes when the average number of calls per 10 minutes is 4.

β’ **Data tables and formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | x | Mean | Cumulative | Result |

2 | 5 | 4 | TRUE | =POISSON.DIST(A2,B2,C2) |

3 | 0.785 |

β’ **Explanation**: Given an average of 4 calls every 10 minutes, the cumulative probability of receiving up to 5 calls in that time frame is 78.5%.

π **Example 5**:

β’ **Purpose of example**: Determine the probability of a factory producing exactly 7 defective items daily when the average number of faulty items made daily is 5.

β’ **Data tables and formulas**:

A | B | C | D | |
---|---|---|---|---|

1 | x | Mean | Cumulative | Result |

2 | 7 | 5 | FALSE | =POISSON.DIST(A2,B2,C2) |

3 | 0.104 |

β’ **Explanation**: Given an average production of 5 defective items daily, the probability of the factory producing exactly 7 defective items on a particular day is 10.4%.

π **Example 6: Using POISSON.DIST with IF**

β’ **Purpose of example**: Determine if a server faces 15 requests per minute on average and is under unusual load if it receives 25 recommendations in a minute.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 25 | 15 | TRUE | =IF(POISSON.DIST(A2,B2,C2)>0.95, “Unusual”, “Normal”) | Unusual |

β’ **Explanation**: If the cumulative probability of receiving 25 or fewer requests is greater than 95%, it’s considered unusual. In this case, the server receiving 25 recommendations in a minute is deemed unique.

π **Example 7: Using POISSON.DIST with SUM**

β’ **Purpose of example**: Calculate the combined probability of a bookstore selling 2, 3, or 4 rare books daily when the average sale is 3 rare books daily.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 2 | 3 | FALSE | =SUM(POISSON.DIST(A2,B2,C2), POISSON.DIST(A3,B3,C3), POISSON.DIST(A4,B4,C4)) | 0.647 |

3 | 3 | 3 | FALSE | ||

4 | 4 | 3 | FALSE |

β’ **Explanation**: The individual probabilities of selling 2, 3, or 4 rare books are summed up to get a combined probability. The combined chance of selling 2 to 4 rare books daily is 64.7%.

π **Example 8: Using POISSON.DIST with VLOOKUP**

β’ **Purpose of example**: Given a table of average sales, determine the probability of selling 5 products of a specific type per day.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | Product Type | x | Cumulative | Formula | Result |

2 | Type A | 5 | FALSE | =POISSON.DIST(B2,VLOOKUP(A2,F:G,2,FALSE),C2) | 0.175 |

3 |

Average Sales Table:

F | G | |
---|---|---|

1 | Product Type | Average Sales |

2 | Type A | 4 |

3 | Type B | 6 |

β’ **Explanation**: The `VLOOKUP`

function fetches the average “Type A” sales from the Average Sales Table. The `POISSON.DIST`

function then calculates the probability of selling exactly 5 “Type A” products in a day. The result is a 17.5% chance.

π **Example 9: Using POISSON.DIST with AVERAGE**

β’ **Purpose of example**: Calculate the average probability of a bakery selling 3, 4, or 5 pastries in an hour when the average sale is 4 pastries per hour.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 3 | 4 | FALSE | =AVERAGE(POISSON.DIST(A2,B2,C2), POISSON.DIST(A3,B3,C3), POISSON.DIST(A4,B4,C4)) | 0.238 |

3 | 4 | 4 | FALSE | ||

4 | 5 | 4 | FALSE |

β’ **Explanation**: The individual probabilities of selling 3, 4, or 5 pastries are averaged. The average probability of selling between 3 to 5 pastries in an hour, given an average sale of 4 pastries, is 23.8%.

π **Example 10: Using POISSON.DIST with MAX**

β’ **Purpose of example**: Determine the highest probability of a website receiving 10, 11, or 12 visitors in an hour when the average number of visitors is 10.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 10 | 10 | FALSE | =MAX(POISSON.DIST(A2,B2,C2), POISSON.DIST(A3,B3,C3), POISSON.DIST(A4,B4,C4)) | 0.125 |

3 | 11 | 10 | FALSE | ||

4 | 12 | 10 | FALSE |

β’ **Explanation**: The `MAX`

function is used to determine the highest probability among the three scenarios. The highest probability is for the website to receive precisely 10 visitors in an hour, which is 12.5%.

π **Example 11: Using POISSON.DIST with MIN**

β’ **Purpose of example**: Determine the lowest probability of a call center receiving 6, 7, or 8 calls in 10 minutes when the average number is 7.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 6 | 7 | FALSE | =MIN(POISSON.DIST(A2,B2,C2), POISSON.DIST(A3,B3,C3), POISSON.DIST(A4,B4,C4)) | 0.137 |

3 | 7 | 7 | FALSE | ||

4 | 8 | 7 | FALSE |

β’ **Explanation**: The `MIN`

function is used to determine the lowest probability among the three scenarios. The lowest probability is for the call center to receive 6 calls in 10 minutes, which is 13.7%.

π **Example 12: Using POISSON.DIST with ROUND**

β’ **Purpose of example**: Calculate the rounded probability of a factory producing 5 defective items daily when the average number of faulty items is 4.

β’ **Data tables and formulas**:

A | B | C | D | E | |
---|---|---|---|---|---|

1 | x | Mean | Cumulative | Formula | Result |

2 | 5 | 4 | FALSE | =ROUND(POISSON.DIST(A2,B2,C2), 3) | 0.157 |

β’ **Explanation**: The `ROUND`

function is used to round the result of the `POISSON.DIST`

function to three decimal places. The rounded probability of the factory producing exactly 5 defective items in a day, given an average of 4 faulty items, is 15.7%.

**Part 3: Tips and tricks**:

- π The
`POISSON.DIST`

function is handy for rare events in large datasets. - π Ensure that the mean value you provide is non-negative. A negative mean will result in an error.
- π Remember that the Poisson distribution assumes each event is independent of the others.
- π For a visual representation, consider plotting the Poisson distribution using Excel’s charting tools.

- π When using the
`POISSON.DIST`

function, ensure your dataset is large enough to provide a meaningful result. - π The
`POISSON.DIST`

function can be used to model random and independent events, such as the number of phone calls to a call center or the number of emails received in an hour. - π If you’re interested in the number of events over a different time frame or space, adjust the mean value accordingly.
- π For more advanced statistical analysis, consider using other distribution functions in Excel or specialized statistical software.