Google Sheets BINOM.INV Function: Syntax, Examples & Tips

What Is BINOM.INV?

BINOM.INV works backwards from a probability to a count. Given a number of trials and the success rate on each, it returns the smallest number of successes k for which the chance of getting k or fewer successes is at least your target probability. It is the inverse of the cumulative binomial distribution calculated by BINOMDIST (or BINOM.DIST) with cumulative set to TRUE.

A practical way to read the answer: "95% of the time, we will see this many successes or fewer." That makes BINOM.INV a handy tool for capacity planning, quality limits, and setting thresholds.

The older function CRITBINOM performs the same calculation with the same arguments.

How BINOM.INV Works

Google Sheets steps through possible counts 0, 1, 2, and so on, adding up binomial probabilities, and returns the first count at which the running total reaches target_prob. Because the binomial distribution is discrete, the cumulative probability at the returned value will usually be somewhat higher than the target, not exactly equal.

Basic Examples

Median number of heads

=BINOM.INV(10, 0.5, 0.5)

Returns 5. The probability of 5 or fewer heads in 10 flips is about 62%, the first count to pass 50%.

A 95% upper limit

=BINOM.INV(100, 0.05, 0.95)

Returns 9. With a 5% defect rate, a batch of 100 will contain 9 or fewer defects at least 95% of the time.

Capacity planning

=BINOM.INV(B1, B2, 0.99)

With 400 bookings in B1 and a 90% show-up rate in B2, this returns the number of seats needed to seat everyone who shows up on 99% of days.

Verify the result

=BINOMDIST(BINOM.INV(100, 0.05, 0.95), 100, 0.05, TRUE)

Shows the actual cumulative probability at the returned count (here about 0.97).

Reading the Result Correctly

Common Use Cases

• Quality control: set an acceptance limit for defects in a sample
• Staffing and seating: how many slots to provide for uncertain attendance
• Marketing: a realistic upper range for responses from a campaign
• Inventory: how many units of a slow-moving item might be demanded
• Fraud and anomaly checks: flag a day when a count is above the 99th percentile

Common Errors

• #NUM!: prob_success or target_prob is outside 0 to 1, or num_trials is negative.
• #VALUE!: An argument is text that cannot be read as a number.
• Expecting an exact match: The result is the first count that meets or passes the target, so its cumulative probability is usually higher than target_prob.
• Confusing the order of arguments: The number of trials comes first, then the success rate, then the target.

Tips and Best Practices

• Use BINOM.INV to turn a percentage rule into a whole-number limit that operations teams can act on.
• Pair it with IF to flag outliers: =IF(C2 > BINOM.INV(B2, 0.05, 0.99), "Investigate", "").
• For a continuous proportion rather than a count, see BETAINV.

Related Functions

• BINOMDIST: probability of a given number of successes
• BETAINV: percentiles of a proportion
• AVERAGE: the mean of a data set
• ROUND: tidy up probability results
• IF: turn a probability into a decision