A to Z of Excel Functions: The CRITBINOM Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CRITBINOM function.

The CRITBINOM function

In probability theory and statistics, the binomial distribution with parameters n and p is the discrete probability distribution of the number of successes in a sequence of n independent success / failure experiments, each of which yields success with probability p. For the record, a success / failure experiment is also called a Bernoulli experiment or Bernoulli trial. The binomial distribution is frequently used to model the number of successes in a sample of size n drawn with replacement from a population of size N.

This function returns the smallest value for which the cumulative binomial distribution which is greater than or equal to a criterion value. This might sound like gobbledygook, but it is useful for creating independent simulations analysis in Excel (please see Simulation Stimulation for more information).

For example, the chart (below) 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.  

The CRITBINOM function employs the following syntax to operate:

The CRITBINOM function has the following arguments:

• trials: this is required and represents the number of Bernoulli trials
• probability_s: this is also required. This is the probability of a success on each trial
• alpha: again, required. This represents the aforementioned criterion value.

It should be further noted that:

• this function has been replaced with a newer function (BINOM.INV) that may provide improved accuracy and whose name better reflects its usage.  Although this function is still available for backward compatibility, you should consider using the new functions going forward, since this function may not be available in future versions of Excel
• if any argument is nonnumeric, CRITBINOM returns the #VALUE! error value
• if trials is not an integer, it is truncated
• if trials < 0, CRITBINOM returns the #NUM! error value
• if probability_s is < 0 or probability_s > 1, CRITBINOM returns the #NUM! error value
• if alpha < 0 or alpha > 1, CRITBINOM returns the #NUM! error value.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.