How to Use the POISSON.DIST Function in Google Sheets to Model Probability Distributions

Predicting the frequency of discrete, independent events occurring over a fixed interval of time or space is a fundamental challenge in business analytics, logistics, web traffic forecasting, and customer service operations. In Google Sheets, the POISSON.DIST function provides a mathematically rigorous tool for calculating probabilities under the Poisson distribution, assuming events occur at a known constant average rate.

The Mathematical Framework of the POISSON.DIST Function

The Poisson distribution applies when individual events occur independently within continuous intervals—such as server requests per minute, helpdesk tickets per hour, customer arrivals per checkout lane, or inventory defects per manufacturing run. In Google Sheets, POISSON.DIST replaces the legacy POISSON function with standardised syntax and enhanced numerical precision.

The syntax for POISSON.DIST is defined as follows:

=POISSON.DIST(x, mean, cumulative)

The function requires three explicit parameters:

• x: The number of discrete event occurrences being evaluated (must be an integer greater than or equal to 0; decimal inputs are automatically truncated).
• mean: The expected average number of occurrences (λ, or lambda) over the specified interval. Must strictly be a positive real number (mean > 0).
• cumulative: A logical boolean flag determining the output form:
  • FALSE: Returns the probability mass function (PMF), computing the exact probability that precisely x events will occur.
  • TRUE: Returns the cumulative distribution function (CDF), computing the probability that at most x events will occur (from 0 up to x).

Probability Mass Function (Exact) vs Cumulative Distribution Function

Choosing between exact and cumulative probabilities is critical for proper statistical modeling in spreadsheets:

Analytical Question	Formulation	Google Sheets Formula (Mean = 5)	Statistical Purpose
What is the probability of exactly 4 events?	P(X = 4)	=POISSON.DIST(4, 5, FALSE)	Exact PMF evaluation
What is the probability of at most 4 events?	P(X ≤ 4)	=POISSON.DIST(4, 5, TRUE)	Cumulative lower-tail CDF
What is the probability of more than 4 events?	P(X > 4)	=1 - POISSON.DIST(4, 5, TRUE)	Upper-tail survival function
What is the probability of between 3 and 7 events?	P(3 ≤ X ≤ 7)	=POISSON.DIST(7, 5, TRUE) - POISSON.DIST(2, 5, TRUE)	Interval probability range

Step-by-Step Practical Application in Google Sheets

To examine a real-world scenario, consider an e-commerce infrastructure team monitoring incoming payment gateway API calls. Historic telemetry confirms that the gateway receives an average of 12 requests per second during peak hours.

1. Setting Up the Parameter Block

In a fresh sheet, establish baseline parameter cells:

• Cell B1: Label Expected Rate (λ)
• Cell B2: Value 12
• Cell C1: Label Observed Event Target (x)
• Cell C2: Value 15

2. Calculating Exact Event Probability

To determine the exact likelihood of receiving precisely 15 requests in any given second, enter this formula in cell D2:

=POISSON.DIST(C2, B2, FALSE)

Google Sheets returns approximately 0.0724 (or 7.24%), showing that despite an average of 12, there is a measurable chance of exactly 15 requests arriving.

3. Calculating Capacity Breach Probability

If the payment microservice is configured with a concurrency threshold that throttles connections when requests exceed 18 per second, calculating the probability of a system overload requires an upper-tail formula:

=1 - POISSON.DIST(18, B2, TRUE)

This subtracts the cumulative probability of 0 through 18 requests from 1, yielding approximately 0.0345 (or 3.45%). System architects can use this metric to decide whether auto-scaling groups must be expanded.

Constructing an Automated Probability Distribution Table

Visualising probabilities across a complete range of potential event counts allows decision-makers to evaluate risk profiles effectively:

1. In column A (cells A5:A25), enter sequence values from 0 to 20 representing candidate values of x.
2. In cell B5, enter the formula for individual probability and drag downward:

   =POISSON.DIST(A5, $B$2, FALSE)

3. In cell C5, enter the cumulative probability formula and drag downward:

   =POISSON.DIST(A5, $B$2, TRUE)

4. Highlight cells A5:B25 and select Insert > Chart. Set the chart type to a Column Chart to produce an immediate visual histogram of your Poisson distribution curve.

Troubleshooting Common Formula Errors

When working with POISSON.DIST in Google Sheets, watch for specific calculation barriers:

• #NUM! Error: Occurs if x is negative (x < 0) or if the mean is less than or equal to zero. Poisson distributions require non-negative event counts and strictly positive arrival rates.
• #VALUE! Error: Triggered when non-numeric text characters are provided in either the x or mean arguments, or if the cumulative flag is not recognised as a valid boolean.