Poisson distribution in Excel: counting events, and when to use negative binomial

Guide · Updated October 2026

In short: how many times something happens in a week, a month or a year, when events come one at a time at a known average rate.

The Poisson distribution gives the number of events in a fixed period, such as complaints in a week or breakdowns in a month, when they occur independently at a known average rate. One number sets it, the mean. With 4 complaints a week on average, a week without any has a 1.8% chance and a week with 8 or more 5.1%. Excel has POISSON.DIST but no inverse; random counts come from a small table of cumulative probabilities. When counts vary more than a Poisson allows, use the negative binomial.

What a Poisson distribution is

Picture events that arrive at random, one at a time, at a steady average rate: calls, defects, claims, failures of a large fleet. The number that falls in a week is a whole number, 0, 1, 2 and so on, and its distribution depends only on the average count. The average also fixes how much the weeks vary: the standard deviation is the square root of the mean, 2 for 4 complaints a week. The time between such events is exponential: here 1.75 days on average.

How to read the chart: each bar is the share of the 10,000 simulated weeks with that number of complaints, and each dot the exact probability. Bars and dots agree.

The most likely weeks have 3 or 4 complaints (19.5% each). The P10 is 2 and the P90 7: at least eight weeks in ten fall between them.

The Poisson distribution in Excel

POISSON.DIST(k, mean, FALSE) gives the probability of exactly k events, and with TRUE the probability of at most k: =1-POISSON.DIST(7, 4, TRUE) is the 5.1% chance of 8 or more. There is no POISSON.INV, so a random count needs a step more. The workbook lists the cumulative probabilities of 0 to 99 events on its Tables sheet and counts how many of them are below the random number u: that count is the smallest k whose cumulative probability reaches u, which is exactly a Poisson draw. In one cell: =COUNTIF(Tables!B$3:B$102,"<"&RAND()).

Negative binomial: when counts vary more than a Poisson allows

Real counts are often more uneven than a Poisson: demand has busy and quiet months, defects come in batches, claims rise in bad weather. Check the history: if the counts vary much more than a Poisson allows (their variance, the standard deviation squared, is clearly larger than their mean), a Poisson understates both the quiet and the busy periods. The negative binomial adds a second parameter for that extra spread. With the same mean of 4 but a variance of 8:

How to read the chart: each line joins the probabilities of 0, 1, 2 and more events in a week. The negative binomial is higher at both ends: more weeks with no event and more weeks with many.

In Excel, NEGBINOM.DIST(k, n, p, TRUE) gives its cumulative probabilities and the same table trick gives draws: =COUNTIF(Tables!C$3:C$102,"<"&RAND()). Its parameters are n and p (the count of failures before the n-th success, in the textbook reading). From a mean and a variance: p = mean / variance and n = mean² / (variance − mean), here p = 0.5 and n = 4. The reorder point example draws its monthly demand from a negative binomial for this reason.

When to use Poisson, negative binomial or binomial

• Poisson for counts of independent events at a steady rate, with no upper limit in practice: arrivals, calls, defects, failures of a large population.
• Negative binomial for the same kind of count when the history varies more than its mean: demand, claims, defects that cluster.
• Binomial when there is a fixed number of chances: defective units in a batch of 50, customers out of 200 who accept an offer.

Entering a Poisson distribution in xellstorm

1. Make the cell an input and, on the Distributions step, choose Poisson and type its mean; or choose Negative binomial and type n and p.
2. With past counts, select the input and use “From data” in the side panel: for whole numbers it fits Poisson and negative binomial next to the continuous shapes and ranks them. When the counts vary no more than their mean, it skips the negative binomial and says to use a Poisson.

Add-in models convert from RiskPoisson, VosePoisson and PsiPoisson, and from RiskNegbin, VoseNegBin and PsiNegBinomial.

Questions

Related

• Guide

  Which probability distribution should I use? A guide for Monte Carlo models

  Find the right probability distribution in two questions: PERT, triangular, normal, lognormal, Weibull, Poisson, binomial, beta and more, with what each is for and its Excel formula.
• Guide

  Binomial and Bernoulli distributions in Excel: yes-or-no events and counts out of n

  A Bernoulli distribution is one yes-or-no event, such as a risk that occurs or not; a binomial counts the yeses out of n tries. Excel formulas, BINOM.DIST and BINOM.INV, and risk registers.
• Inventory policy

  Which reorder point balances cost against stockouts?

  Raising the reorder point from 180 to 250 units cuts the chance of a stockout year from 28.0% to 5.1% and adds 345 to an average annual cost of 2,133.

xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.