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.
|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
|2||10||Number of failures|
|3||4||Threshold number of successes|
|4||0.25||Probability of a success|
|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:
|#NUM!||Occurs if either:
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:
where, x is number_f, r is number_s, and p is probability_s.
See Wikipedia for more information on negative binomial distribution.