NEGBINOM.DIST function

The NEGBINOM.DIST function in Excel returns the probability of a given number of failures before a specified number of successes occurs in a negative binomial distribution. This distribution is useful in scenarios where you’re counting the number of failures before achieving a specific number of successes, such as in quality control or reliability testing.

Syntax:

NEGBINOM.DIST(x, trials, probability_s, cumulative)

Arguments:

• x: Required. The number of failures before the specified number of successes occurs. It must be a non-negative integer.
• trials: Required. The number of successes to achieve. It must be a positive integer.
• probability_s: Required. The probability of success on each trial. It must be a number between 0 and 1.
• cumulative: Required. A logical value that determines the form of the function:
  • TRUE: Returns the cumulative distribution function (CDF), which gives the probability that the number of failures is less than or equal to x.
  • FALSE: Returns the probability mass function (PMF), which gives the probability of exactly x failures before achieving the specified number of successes.

Output:

• The function returns the probability associated with the negative binomial distribution.
  • When cumulative = TRUE, the function returns the cumulative probability of having up to x failures before achieving the trials successes.
  • When cumulative = FALSE, the function returns the probability of having exactly x failures before achieving the trials successes.

How It Works:

• The negative binomial distribution describes the probability of achieving a fixed number of successes, with the number of failures occurring before reaching that number being the variable of interest.
• The function calculates either the probability of exactly x failures or the cumulative probability of having up to x failures before achieving the specified number of successes.

Example 1: Exact Number of Failures

Suppose you are conducting an experiment where the probability of success on each trial is 0.2, and you want to find the probability of exactly 3 failures before achieving 5 successes.

Use the formula:

=NEGBINOM.DIST(3, 5, 0.2, FALSE)

This will return the probability of having exactly 3 failures before achieving 5 successes.

Example 2: Cumulative Probability of Failures

Suppose you have the same experiment, but now you want to find the cumulative probability of having up to 3 failures before achieving 5 successes.

Use the formula:

=NEGBINOM.DIST(3, 5, 0.2, TRUE)

This will return the cumulative probability of having 0, 1, 2, or 3 failures before achieving 5 successes.

Example 3: No Failures Before Successes

If you want to calculate the probability of having no failures before achieving 5 successes, use the formula:

=NEGBINOM.DIST(0, 5, 0.2, FALSE)

This will return the probability of exactly 0 failures before achieving 5 successes.

Key Points:

• NEGBINOM.DIST is useful in scenarios where you’re tracking failures before reaching a target number of successes, such as in quality control, marketing (successes being sales or sign-ups), or manufacturing (successes being defect-free items).
• The argument cumulative allows you to choose between the exact probability and the cumulative probability.
• The probability of success (probability_s) must be between 0 and 1.

Use Cases:

• Business/Marketing: Calculate the probability of achieving a specific number of sales (successes) after a certain number of failed attempts (such as customer rejections or marketing campaigns).
• Quality Control: Analyze how many defective items (failures) occur before reaching the desired number of non-defective items (successes).
• Medical Studies: Model the number of failures (e.g., patients not recovering) before a set number of recoveries (successes) occurs.

Notes:

• NEGBINOM.DIST was introduced in Excel 2010 and is available in later versions.
• The trials argument (number of successes) must always be a positive integer.