A to Z of Excel Functions: The NEGBINOMDIST Function

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

The NEGBINOMDIST 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. 

You’re probably seeing the similarities between this article and the last one.  This function also 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 NEGBINOMDIST 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, NEGBINOMDIST can calculate the probability that exactly two babies born are male before one female baby is born.

The NEGBINOMDIST function employs the following syntax to operate:

The NEGBINOMDIST 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 on each trial.

Unlike NEGBINOM.DIST, this function does not have the cumulative argument.

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, NEGBINOMDIST returns the #VALUE! error value
• if number_f < 0 or number_s < 1, NEGBINOMDIST returns the #NUM! error value
• if probability_s < 0 or probability_s > 1, NEGBINOMDIST 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.

Please see my example below: 

Essentially, NEGBINOMDIST is a slightly less flexible version of NEGBINOM.DIST and represents the version of the function used before its successor was introduced in Excel 2010.  It is still recognised in Excel for legacy reasons.  Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

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.