Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Critbinom-Function

The Excel CRITBINOM Function

Related Function:
BINOMDIST
NEGBINOMDIST
BINOMDIST and CRITBINOM

The Cumulative Binomial Distribution (calculated by the Excel Binomdist 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 Critbinom function calculates the inverse of the Cumulative Binomial Distribution.

I.e. for a given number of independent trials, the Critbinom 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

CRITBINOM and BINOM.INV

In Excel 2010, the Critbinom function has been replaced by the Binom.Inv function, which has improved accuracy.

Although it has been replaced, the Critbinom function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

Function Description

The Excel CRITBINOM function returns the inverse of the Cumulative Binomial Distribution. I.e. for a specific 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 specified value.

For example, the Critbinom function could be used to find the smallest number of tosses of a coin for which there is a 50% chance of at least 20 heads.

The syntax of the function is:

CRITBINOM( trials, probability_s, alpha )

where the arguments are:

trials -

The number of independent trials that are to be done.

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


Critbinom 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 for 100 tosses of a coin. This curve represents the probability that at most x heads will be thrown from the 100 tosses.

The Excel Critbinom function calculates the inverse of the curve on the right. I.e. the Critbinom function calculates the minimum value of x for which the Cumulative Binomial Distribution function is a specified value. This is shown in the spreadsheets below, for three different criterion values.

 Formulas:
  A
1 =CRITBINOM( 100, 0.5, 20% )
2 =CRITBINOM( 100, 0.5, 50% )
3 =CRITBINOM( 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%

Note that the probability_s and the alpha arguments can be entered as decimal values or as percentages.

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


Critbinom Function Errors

If you get an error from the Excel Critbinom 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