The NEGBINOM.DIST function returns the negative binomial distribution, the probability that there will be a given number of failures before a required number of successes is achieved.

For example, you need to find 10 people with excellent reflexes, and you know the probability that a candidate has these qualifications is 0.25. NEGBINOM.DIST calculates the probability that you will interview a certain number of unqualified candidates before finding all 10 qualified candidates.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • The NEGBINOM.DIST function replaces the NEGBINOMDIST function included in earlier versions of Excel.

Syntax

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

Arguments

Argument Description
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 value that determines the form of the function

  TRUE Returns the cumulative distribution function
  FALSE Returns the probability density function

Examples

  A B C D
1 Data Description    
2 10 Number of failures    
3 4 Threshold number of successes    
4 0.25 Probability of a success    
5        
6 Formula Result Notes
7 =NEGBINOM.DIST(A2,A3,A4,TRUE) 0.47866 Cumulative negative binomial distribution for the terms above
8 =NEGBINOM.DIST(A2,A3,A4,FALSE) 0.062913 Probability negative binomial distribution for the terms above

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either:

  the number_fnumber_s, or probability_s arguments are not recognized as numeric values
  the supplied cumulative argument is not recognized as a numeric or a logical value
#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

The binomial distribution is a statistical measure frequently used to indicate the probability of a specific number of successes occurring from a specific number in independent trials. 

The negative binomial distribution calculates the probability of a given number of failures occurring before a fixed number of successes, i.e. the number of successes is fixed and the number of trials varies.

The following two forms are used:

  • Probability Mass Function – calculates the probability of there being exactly f failures before s successes
  • Cumulative Distribution Function – calculates the probability of there being at most f failures before s successes.

The equation for the negative binomial distribution is:    

    \[    nb(x;r,p) = {x + r - 1 \choose r - 1} \acute{p}(1-p)^N    \]

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

See Wikipedia for more information on negative binomial distribution.