Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Binom.Inv-Function

The Excel BINOM.INV Function

Related Functions:
BINOM.DIST
NEGBINOM.DIST
Inverse Cumulative Binomial Distribution

The Cumulative Binomial Distribution (calculated by the Excel Binom.Dist function) is a statistical measure that is frequently used to indicate the probability of obtaining a specific number of successes from a specific number of independent trials.

The Binom.Inv function calculates the inverse of the Cumulative Binomial Distribution.

I.e. for a given number of independent trials, the Binom.Inv function returns the smallest value of x (the number of successes) for a specified Cumulative Binomial Distribution probability.

Further information on the Binomial Distribution can be found on the Wikipedia Binomial Distribution page

Function Description

The Excel Binom.Inv function returns the inverse of the Cumulative Binomial Distribution.

I.e. for a given number of independent trials, the function returns the smallest value (number of successes) for which the cumulative binomial distribution is greater than or equal to a supplied probability.

For example, the Binom.Inv function could be used to calculate the minimum number of tosses of a coin for which there is a 50% chance of at least 20 heads.

The Binom.Inv function is new in Excel 2010, and so is not available in earlier versions of Excel. However, this function is simply an updated version of the Critbinom function, which is available in earlier versions of Excel.

The syntax of the Binom.Inv function is:

BINOM.INV( trials, probability_s, alpha )

where the arguments are listed in the table below:

trials -

The number of independent trials to be done.

(If supplied as a decimal, this value is truncated to an integer by Excel)
probability_s - The probability of success in a single trial.
alpha - The probability of the Cumulative Binomial Distribution (must be between 0 and 1).

Binom.Inv Function Example

Binomial Cumulative Distribution Function Chart
Cumulative Distribution Function for 100 Tosses of a Coin

The above chart on the right shows the Cumulative Binomial Distribution Function, representing the probability that at most x heads will be thrown from 100 tosses of a coin.

The Excel Binom.Inv function calculates the inverse of this function. I.e. the function calculates the minimum value of x for which the Cumulative Binomial Distribution function is a given probability. This is shown in the spreadsheet below, for three different probability values.

 Formulas:
  A
1 =BINOM.INV( 100, 0.5, 20% )
2 =BINOM.INV( 100, 0.5, 50% )
3 =BINOM.INV( 100, 0.5, 90% )
 Results:
  A B
1 46   - min. no. tosses for which the Binom. Cum. Dist = 20%
2 50   - min. no. tosses for which the Binom. Cum. Dist = 50%
3 56   - min. no. tosses for which the Binom. Cum. Dist = 90%

For further examples of the Excel Binom.Inv function, see the Microsoft Office website.


Binom.Inv Function Errors

If you get an error from the Excel Binom.Inv Function, this is likely to be one of the following:

Common Errors
#NUM! -

Occurs if either:

  • The supplied trials argument is < 0;
  • The supplied probability_s argument is < 0 or > 1;
  • The supplied alpha argument is < 0 or > 1.
#VALUE! - Occurs if any of the supplied arguments are non-numeric.
Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions