The `NEGBINOM.DIST` function in Excel is used to return the negative binomial distribution, which is useful for modeling the number of failures before achieving a specified number of successes in a series of independent and identically distributed Bernoulli trials. Here’s how you can use this function:
Syntax
NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)
Parameters
- `number_f`: Required. The number of failures you want (non-negative integer).
- `number_s`: Required. The number of successes you are targeting (non-negative integer).
- `probability_s`: Required. The probability of success on a single trial (between 0 and 1).
- `cumulative`: Required. A logical value that determines the form of the function.
- If `TRUE`, `NEGBINOM.DIST` returns the cumulative distribution function, which is the probability that there are at most `number_f` failures before `number_s` successes.
- If `FALSE`, it gives the probability mass function, which is the probability that there are exactly `number_f` failures before `number_s` successes.
Example
Suppose you want to find the probability of having exactly 3 failures before achieving 5 successes in a process where the probability of success on each trial is 0.6. You would set `cumulative` to `FALSE` to get a probability mass function.
Place the following formula in a cell:
=NEGBINOM.DIST(3, 5, 0.6, FALSE)
If you want the cumulative probability (the probability of having at most 3 failures before achieving 5 successes), change `cumulative` to `TRUE`:
=NEGBINOM.DIST(3, 5, 0.6, TRUE)
Notes
- Ensure that your `number_f` and `number_s` values are non-negative integers and that `probability_s` is a decimal between 0 and 1.
- This function is available in Excel 2010 and later versions.
- The function is especially useful in quality control and other statistical analyses involving repeat trials until a certain condition is met.
Unlock Your Potential
Excel
Access
Power BI
