How to use the BINOM.DIST() function in Excel

This function calculates probabilities for a binomial distribution, which models scenarios with:

• A fixed number of trials (trials).
• Only two outcomes per trial: success or failure.
• Independent trials with a constant success probability (probability_s).

Syntax

BINOM.DIST(number_s; trials; probability_s; cumulative)

Example Use Case:

• Calculating the probability that 50 out of 100 people support a smoking ban, given each has a 60% chance of supporting it.

Arguments

Background

1. Binomial Distribution Basics:
   • Models counts of successes in n independent Bernoulli trials (e.g., coin flips, survey responses).
   • Probability Mass Function (PMF):

1. • • (nk)(kn​) = Combination of n trials with k successes (COMBIN(n, k) in Excel).
     • p = Success probability per trial.
2. Key Properties:
   • Mean (Expected Value): μ=n×pμ=n×p
   • Variance: σ2=n×p×(1−p)σ2=n×p×(1−p)
3. Bernoulli Process:
   • Named after mathematician Jakob Bernoulli.
   • Each trial is independent with outcomes 1 (success) or 0 (failure).

Examples

1. Vacation Directions (Yes/No Survey)

Problem:

• You ask 100 strangers for directions, with a 50% chance (p = 0.5) each says « yes. »
• What’s the probability that exactly 66 answer « yes »?

Formula:

BINOM.DIST(66; 100; 0.5; FALSE)  // Exact probability

Result: 0.05% (see Figure below).

Cumulative Probability (≤66 yes answers):

BINOM.DIST(66, 100, 0.5, TRUE)   // Returns ~100%

2. Damaged Packages (Quality Control)

Problem:

• A factory produces 2,000 packages, with a 2% defect rate (p = 0.02).
• What’s the probability that exactly 30 are damaged?

Formula:

BINOM.DIST(30; 2000; 0.02; FALSE)  // Exact probability

Result: 1.8% (see Figure below).

Cumulative Probability (≤30 damaged):

BINOM.DIST(30; 2000; 0.02; TRUE)   // Returns 6%

Key Notes

1. When to Use:
   • FALSE: For exact counts (e.g., « What’s the chance of exactly 3 wins in 5 games? »).
   • TRUE: For thresholds (e.g., « What’s the chance of ≤3 wins? »).
2. Assumptions:
   • Trials must be independent (e.g., survey responses don’t influence each other).
   • probability_s must stay constant across trials.
3. Limitations:
   • For large trials (e.g., >1000), consider approximations like the Poisson or Normal distribution.