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

This function returns the minimum number of successes (k) in a binomial distribution where the cumulative probability is ≥ a specified threshold (alpha). It is the inverse of BINOM.DIST().

Syntax

BINOM.INV(trials; probability_s; alpha)

Key Use Case:

• Quality Control: Determine the maximum allowable defective items in a batch before rejecting it.
• Decision-Making: Find critical thresholds for pass/fail scenarios (e.g., survey results, manufacturing tolerances).

Arguments

Background

1. Inverse Binomial Distribution:
   • Solves for k in:

P(X≤k)≥αP(X≤k)≥α

1. • Where:
     • P = Cumulative binomial probability.
     • k = Maximum successes allowed before exceeding the threshold.
2. Assumptions:
   • Trials are independent (e.g., coin flips, quality checks).
   • Only two outcomes per trial (success/failure).
3. Relation to BINOM.DIST():
   • If BINOM.DIST(k, n, p, TRUE) = alpha, then BINOM.INV(n, p, alpha) = k.

Example: Vacation Survey

Scenario:

• You ask 100 people for directions, each with a 50% chance (p = 0.5) of answering « yes. »
• Question: What is the maximum number of « yes » responses (k) where the cumulative probability ≤ 0.1% (alpha = 0.001)?

Formula:

BINOM.INV(100, 0.5, 0.001)

Result: 35 (see Figure below).

Interpretation:

• There is a 0.1% chance that 35 or fewer people would say « yes » by random chance.
• If you observe >35 « yes » answers, the result is statistically significant (exceeds the threshold).

Key Notes

1. Quality Control Application:
   • If a batch of 1,000 parts has a 2% defect rate (p = 0.02), use BINOM.INV() to find the maximum defects allowed before rejecting the batch (e.g., for alpha = 0.95).
2. Threshold Logic:
   • Lower alpha = Stricter criteria (fewer allowed successes).
   • Higher alpha = More lenient criteria (more allowed successes).
3. Limitations:
   • For large trials (e.g., >1,000), consider approximations like the Normal distribution.