The Excel NEGBINOM.DIST Function

Related Functions:
BINOM.DIST
BINOM.INV
Binomial and Negative Binomial Distributions

The Binomial Distribution is a statistical measure that is frequently used to indicate the probability of a specific number of successes occurring from a specific number of independent trials.

For the Negative Binomial Distribution, the number of successes is fixed and the number of trials varies. The function calculates the probability of a given number of failures occurring, before a fixed number of successes.

The following two forms are used:

  • The Probability Mass Function - calculates the probability of there being exactly f failures before s successes;
  • The Cumulative Distribution Function - calculates the probability of there being at most f failures before s successes.
Further information can be found on the Wikipedia Negative Binomial Distribution page

Function Description

Given the probability of a success from a single event, the Excel NEGBINOM.DIST function calculates the probability mass function or the cumulative distribution function for the Negative Binomial Distribution.

This gives the probability that there will be a given number of failures before a required number of successes is achieved.

For example, when tossing a fair coin, the Negbinom.Dist function can be used to calculate the probability that you will toss 10 tails before you toss 12 heads.

The Negbinomdist.Dist function is new in Excel 2010, and so is not available in earlier versions of Excel. However the negative binomial probability mass function can be calculated by the Negbinomdist function, which is available in earlier versions of Excel.

The syntax of the Excel Negbinom.Dist function is:

NEGBINOM.DIST( number_f, number_s, probability_s, cumulative )

where the arguments are as follows:

number_f-The number of failures encountered before number_s successes.
number_s-The required number of successes.
probability_s-The probability of success in one trial.
cumulative-

A logical argument that specifies the type of Negative Binomial Distribution to be calculated. This can be either:

TRUE-use the cumulative distribution function;
FALSE-use the probability mass function.

Note that, if the number_f and number_s arguments are input as decimals, these values are truncated to integers.


Negbinom.Dist Function Examples

Example 1 - Probability Mass Function

Binomial Distribution Probability Mass Function Chart
Negative Binomial Distribution Function Showing the Probability of exactly x Tails Before 12 Heads from a Tossed Coin

In the above chart on the right, the Negative Binomial Probability Mass Function is used to show the probability that a tossed coin will result in exactly x tails before there are 12 tossed heads.

The spreadsheets below show the Excel Negbinom.Dist function used to evaluate this function for three different values of x.

Clearly, the probability of tossing a head on any one trial is 0.5, so this is provided as the value of the probability_s argument.

 Formulas:
 A
1=NEGBINOM.DIST( 6, 12, 0.5, FALSE )
2=NEGBINOM.DIST( 12, 12, 0.5, FALSE )
3=NEGBINOM.DIST( 15, 12, 0.5, FALSE )
 Results:
 AB
10.047210693  - Probability of exactly 6 tails before 12 heads
20.080590129  - Probability of exactly 12 tails before 12 heads
30.057564378  - Probability of exactly 15 tails before 12 heads

Example 2 - Cumulative Distribution Function

Cumulative Binomial Distribution Function Chart
Cumulative Negative Binomial Distribution Function Showing the Probability of at most x Tails Before 12 Heads from a Tossed Coin

In the above chart on the right, the Cumulative Negative Binomial Function is used to show the probability, that a tossed coin will result in at most x tails before 12 tossed heads.

The spreadsheets below show the Excel Negbinom.Dist function used to evaluate this function for three different values of x.

 Formulas:
 A
1=NEGBINOM.DIST( 6, 12, 0.5, TRUE )
2=NEGBINOM.DIST( 12, 12, 0.5, TRUE )
3=NEGBINOM.DIST( 15, 12, 0.5, TRUE )
 Results:
 AB
10.118942261  - Probability of at most 6 tails before 12 heads
20.580590129  - Probability of at most 12 tails before 12 heads
30.778965831  - Probability of at most 15 tails before 12 heads

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


Negbinom.Dist Function Errors

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

Common Errors
#NUM!-

Occurs if either:

  • The supplied number_f is < 0 or the supplied number_s is < 1;
  • The supplied probability_s is < 0 or > 1.
#VALUE!-

Occurs if either:

  • The number_f, number_s, or probability_s arguments are not recognised as numeric values;
  • The supplied cumulative argument is not recognised as a numeric or a logical value.