The Excel function NEGBINOMDIST calculates the probability of a specified number of successes in a series of independent Bernoulli trials, focusing on the negative binomial distribution. This function is particularly useful in statistics when modeling events that require a certain number of successes before observing a set number of failures, making it a valuable tool in various fields such as economics, epidemiology, and quality control.

Syntax

NEGBINOMDIST(k, r, p, cumulative)

• k: The number of failures until the experiment is stopped.
• r: The number of successes required.
• p: The probability of success on each trial.
• cumulative: A logical value that determines the form of the function. If TRUE, it returns the cumulative probability; if FALSE, it returns the probability mass function.

Example #1

NEGBINOMDIST(3, 2, 0.5, FALSE)

This function call calculates the probability of observing 3 failures before achieving 2 successes with a success probability of 0.5 in each trial. The result is approximately 0.1875.

Example #2

NEGBINOMDIST(4, 3, 0.6, TRUE)

In this example, the function computes the cumulative probability of obtaining 4 failures before 3 successes, where the probability of success in each trial is 0.6. The result is about 0.7972.

Example #3

NEGBINOMDIST(1, 1, 0.2, FALSE)

This call assesses the likelihood of experiencing 1 failure prior to achieving the first success with a success probability of 0.2. The computed probability is approximately 0.8.

Error handling

• VALUE!: This error occurs when a non-numeric value is provided for any of the numerical inputs (k, r, or p).
• NUM!: This indicates invalid numbers, which may happen if k or r is less than 0, or if p is less than 0 or greater than 1. Each of these cases violates the function’s requirements.
• REF!: This error occurs when a cell reference is invalid, which can happen if the referenced cell is deleted or moved.

Conclusion

The NEGBINOMDIST function in Excel is an essential tool for statisticians and analysts dealing with probabilistic models. By understanding its syntax and how to utilize it correctly, users can effectively analyze scenarios involving negative binomial distributions. This capability aids in making informed decisions based on statistical probabilities, enhancing the utility of Excel in professional environments.