04-05: Binomial & Poisson Calculator¶

Home | All Projects | Notes | Exercises | Quiz Hub

Build a reusable discrete-distribution calculator, then use it on real count data: fit a binomial to inspection results and a Poisson to call arrivals, and check whether each model actually fits.

Chapters applied: 06-01 · 06-02 · 12-01

Difficulty: ⭐⭐ Intermediate

The Data¶

File	Contents
`data/batch_defects.csv`	80 batches, 20 items inspected each, count defective — binomial
`data/calls_per_hour.csv`	100 hours of call-centre arrivals — Poisson

What You Produce¶

A binomial calculator: P(X = x), P(X ≤ x), P(X ≥ x), P(a ≤ X ≤ b), plus μ and σ
A Poisson calculator with correct interval rescaling
A full probability table and bar chart for each distribution
An estimate of p from the defect data and of λ from the call data
A goodness-of-fit test for each model — does the theoretical distribution actually match the observed counts?
A comparison showing when the Poisson approximates the binomial

Excel Route¶

Step 1 — The binomial calculator block¶

Set up input cells: B1 = n, B2 = p, B3 = x, B4 = a, B5 = b.

=BINOM.DIST(B3, B1, B2, FALSE)            ' P(X = x)      exactly
=BINOM.DIST(B3, B1, B2, TRUE)             ' P(X <= x)     cumulative
=1-BINOM.DIST(B3-1, B1, B2, TRUE)         ' P(X >= x)     note the x-1
=BINOM.DIST.RANGE(B1, B2, B4, B5)         ' P(a <= X <= b)
=B1*B2                                     ' mean  np
=SQRT(B1*B2*(1-B2))                        ' sd    sqrt(npq)
=BINOM.INV(B1, B2, 0.95)                   ' smallest x with P(X<=x) >= 0.95
=COMBIN(B1,B3)*B2^B3*(1-B2)^(B1-B3)        ' the formula spelled out — same answer

Warning

P(X ≥ x) is 1 − BINOM.DIST(x−1, …, TRUE), not 1 − BINOM.DIST(x, …, TRUE). This off-by-one is the most common error in the whole chapter.

Step 2 — The full table and chart¶

Put x = 0…20 in D2:D22:

=BINOM.DIST(D2, $B$1, $B$2, FALSE)        ' E2: P(x)
=BINOM.DIST(D2, $B$1, $B$2, TRUE)         ' F2: cumulative
=SUMPRODUCT($D$2:$D$22, $E$2:$E$22)       ' check: equals np

Insert ▸ Clustered Column on D:E for the probability histogram.

Step 3 — Poisson calculator¶

H1 = λ, H2 = x, H3 = interval multiplier:

=POISSON.DIST(H2, H1*H3, FALSE)           ' P(X = x)
=POISSON.DIST(H2, H1*H3, TRUE)            ' P(X <= x)
=1-POISSON.DIST(H2-1, H1*H3, TRUE)        ' P(X >= x)
=H1*H3                                     ' mean = variance = lambda'
=SQRT(H1*H3)                               ' sd
=H1^H2*EXP(-H1)/FACT(H2)                   ' the formula spelled out

Set H3 = 20/60 for a 20-minute window from an hourly rate. λ scales with the interval — always.

Step 4 — Fit the models to the data¶

' Binomial: estimate p from the inspection data
=SUM(C2:C81)/SUM(B2:B81)                  ' p-hat = total defective / total inspected
=AVERAGE(C2:C81)                          ' observed mean defectives per batch
=20*p_hat                                 ' expected mean if binomial -> should match
=VAR.S(C2:C81)                            ' observed variance
=20*p_hat*(1-p_hat)                       ' expected variance npq -> should match

' Poisson: estimate lambda from the call data
=AVERAGE(B2:B101)                         ' lambda-hat
=VAR.S(B2:B101)                           ' should be CLOSE TO the mean
=VAR.S(B2:B101)/AVERAGE(B2:B101)          ' dispersion index; ~1 supports Poisson

Step 5 — Goodness-of-fit test¶

For the call data, put the observed counts of 0, 1, 2, … calls in one column and:

=COUNTIF($B$2:$B$101, K2)                 ' L2: observed frequency
=POISSON.DIST(K2, $lambda, FALSE)*100     ' M2: expected frequency
' combine the tail classes until every EXPECTED count is >= 5
=(L2-M2)^2/M2                             ' N2: cell contribution
=SUM(N2:N8)                               ' chi-square statistic
=CHISQ.DIST.RT(chi2, k-1-1)               ' df: k-1, minus 1 for the ESTIMATED lambda

A large p-value here is the good outcome — H₀ is "the Poisson model fits".

R Route¶

Rscript r/analysis.R

Runs both calculators, both fits, both goodness-of-fit tests, and writes distribution_plots.png. See r/analysis.R.

Python Route¶

python python/analysis.py

Same via scipy.stats. See python/analysis.py.

Checkpoints¶

P(X ≥ x) uses x − 1 inside the cumulative call
The full binomial table sums to 1 and its mean equals np
λ is rescaled whenever the interval changes
The Poisson dispersion index (variance ÷ mean) is reported and is near 1
The goodness-of-fit df subtracts one extra for the estimated parameter
Sparse tail classes are combined so every expected count is ≥ 5

Extend It¶

Add a geometric and a hypergeometric section to the calculator
Show the Poisson-approximates-binomial rule numerically: n = 2000, p = 0.0015 vs. Poisson with λ = 3
Add the normal approximation to the binomial with a continuity correction (07-02) and compare all three
Use BINOM.INV to build an acceptance-sampling rule: reject a batch if defectives exceed the 99th percentile