In Excel 2010, the Negbinomdist function has been replaced by the Negbinom.Dist function, which has improved accuracy.
Although it has been replaced, the Negbinomdist function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.
Given the probability of a success from a single event, the Excel NEGBINOMDIST function calculates the Negative Binomial Distribution for a given set of parameters.
This gives the probability that there will be a specified number of failures before a required number of successes is achieved.
For example, given the probability of 50%, that the toss of a coin will land on heads, the Negbinomdist function can be used to calculate the probability that you will toss 10 tails before you toss 12 heads.
The syntax of the function is:
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.|
The above chart on the right shows the Negative Binomial Distribution Function for the probability that the toss of a coin will result in exactly x tails before 12 tossed heads.
The spreadsheets below show the Excel Negbinomdist 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 input as the probability argument to the functions.
For further examples of the Excel Negbinomdist function, see the Microsoft Office website.
If you get an error from the Excel Negbinomdist Function, this is likely to be one of the following:
|#VALUE!||-||Occurs if any of the number_f, number_s, or probability_s arguments are non-numeric.|