BINOM.INV
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value. Replaces the legacy CRITBINOM function.
BINOM.INV(trials, probability_s, alpha)Arguments
trialsThe number of Bernoulli trialsprobability_sThe probability of success on each trialalphaThe criterion value (between 0 and 1)
=BINOM.INV(10, 0.5, 0.75)Smallest k where P(X ≤ k) ≥ 0.75 with n = 10, p = 0.5
=BINOM.INV(20, 0.3, 0.95)95% acceptance threshold for n = 20, p = 0.3
=BINOM.INV(100, 0.5, 0.5)Median number of successes for 100 fair coin flips
- •Used in quality control and acceptance sampling
- •Returns #NUM! if trials < 0, probability_s outside 0..1, or alpha outside 0..1
- •Inverse of BINOM.DIST with cumulative = TRUE
- •Confusing BINOM.INV with BINOM.DIST - BINOM.INV goes from a target probability to a number of successes (the inverse direction), while BINOM.DIST goes from a number of successes to a probability
- •Expecting BINOM.INV to return the exact probability requested by alpha - it returns the smallest number_s where the cumulative probability is greater than or equal to alpha, which usually overshoots slightly rather than matching exactly
- •Passing an alpha, probability_s, or trials value outside their valid ranges (0-1 for the first two, non-negative for trials), which returns a #NUM! error
BINOM.DISTPerforms the forward operation - given a number of successes, returns the corresponding cumulative probability, the inverse of what BINOM.INV calculates.NORM.INVPerforms a similar inverse-probability calculation for the normal distribution, sometimes used as an approximation for BINOM.INV with large trial counts.CRITBINOMThe legacy function that BINOM.INV replaces, with identical behavior under a different, older name.How is BINOM.INV different from BINOM.DIST?
They're roughly inverse operations - BINOM.DIST takes a number of successes and returns a probability, while BINOM.INV takes a target probability (alpha) and returns the smallest number of successes needed to reach or exceed it.
Why doesn't BINOM.INV's result give exactly the probability I specified in alpha?
Because the binomial distribution is discrete, there usually isn't a number_s that hits alpha exactly - BINOM.INV returns the smallest number_s where the cumulative probability is at least alpha, which typically overshoots slightly.
What's BINOM.INV commonly used for?
Quality control and acceptance sampling - for example, determining the maximum number of defective items in a batch that would still be acceptable at a given confidence level.
Need to translate a formula using BINOM.INV?
Use our translator to convert your complete formula