A to Z of Excel Functions: The NEGBINOM.DIST Function

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

The NEGBINOM.DIST function

In probability theory and statistics, the negative binomial distribution is a discrete probability distribution that models the number of successes in a sequence of n independent success / failure experiments, each of which yields success with probability p, before a specified number of failures (denoted r) occur.  For the record, a success / failure experiment is also called a Bernoulli experiment or Bernoulli trial. 

This function returns the negative binomial distribution, the probability that there will be a number_f failures before the number_s-th success, with probability_s probability of a success.  The NEGBINOM.DIST function should be used in problems with a fixed number of successes, when the outcomes of any trial are only success or failure, when trials are independent and when the probability of success is constant throughout the experiment.  For example, NEGBINOM.DIST can calculate the probability that two babies born are male before one female baby is born.

The NEGBINOM.DIST function employs the following syntax to operate:

The NEGBINOM.DIST function has the following arguments:

• number_f: this is required and represents the number of failures in trials
• number_s: this is also required.  This is the threshold number of successes
• probability_s: again, this is required.  This is the probability of success in each trial
• cumulative: the final argument is also required.  This is a logical value that determines the form of the function.  If cumulative is TRUE, then NEGBINOM.DIST returns the cumulative distribution function, which is the probability that there are at most number_f failures; if cumulative is FALSE, it returns the probability density function, which is the probability that there are number_f failures.

It should be further noted that:

• number_f and number_s are truncated to integers
• if number_f, number_s, or probability_s is / are non-numeric, NEGBINOM.DIST returns the #VALUE! error value
• if number_f < 0 or number_s < 1, NEGBINOM.DIST returns the #NUM! error value
• if probability_s < 0 or probability_s > 1, NEGBINOM.DIST returns the #NUM! error value
• the equation for the negative binomial distribution is:

where:

x is number_f, r is number_s and p is probability_s.

Have I lost you yet?  Please see my example below: 

Essentially, NEGBINOM.DIST replaces NEGBINOMDIST.  First introduced in Excel 2010, this provides more compatibility with other statistical software.  The former function is still recognised in Excel though for legacy reasons.

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

A full page of the function articles can be found here.