The following article introduces you to the NEGBINOM.DIST function - one of the functions in the statistical group is very popular in Excel.
Description: The function returns a negative binomial distribution, the probability that there will be Number_f failures before the number_s success attempt , with the probability of success being Probability_s . Support functions from Excel 2010 onwards.
Syntax: NEGBINOM.DIST (number_f, number_s, probability_s, cumulative)
Inside:
- number_f: The number of failures, is a required parameter .
- number_s: Threshold of success, is a required parameter.
- probability_s: Probability of success, is a required parameter.
- cumulative: A logical value that determines the form of the function, which is a required parameter, including:
+ cumulative = True -> returns the cumulative distribution function.
+ cumulative = False -> returns the probability density function.
Attention:
- If number_f, number_s are not integers -> they are truncated to integers.
- Any function argument except cumulative not a number -> function returns the #VALUE! Error value
- If probability_s <0 or probability> 1 -> the function returns the #NUM! Error value
- If number_f <0 or number_s <1 -> the function returns the #NUM! Error value
- The equation for the negative binomial distribution is:
[nbleft ({x; r, p} right) = left ({begin {array} {* {20} {c}}
{x + r - 1}
{r - 1}
end {array}} right) p ' {left ({1 - p} right) ^ N}]
Inside:
x is number_f, r is number_s, and p is probability_s.
For example:
Find the cumulative negative binomial distribution and the probability with the values in the following data table:
- Calculate cumulative negative binomial distributions (corresponding to True values ) . In a cell to calculate, enter the formula : = NEGBINOM.DIST (D6, D7, D8, D9)
- Press Enter -> cumulative negative binomial distribution is:
- Calculate probability binomial distribution (corresponding to False value ) . In a cell to calculate, enter the formula : = NEGBINOM.DIST (D6, D7, D8, D10)
- Press Enter -> negative binomial distribution probability is:
- There is a slight difference in value between cumulative negative binomial distribution function and probability negative binomial distribution.
Above are instructions and some specific examples when using the NEGBINOM.DIST function in Excel.
Good luck!