NEGBINOMDIST

Calculates probabilities for a negative binomial distribution.

Syntax:

NEGBINOMDIST(x, r, p)

For independent trials each with a probability p of success, NEGBINOMDIST returns the probability that there will be exactly x failures before there have been r successes. The formula used is:

$(x + r - 1 r - 1) p^{r} (1 - p)^{x} = \frac{( x + r - 1 )!}{x ! ( r - 1 )!} p^{r} (1 - p)^{x}$

Example:

NEGBINOMDIST(1, 1, 0.5)

returns 0.25 (25%), the probability that heads will come up exactly once before tails has come up when tossing a coin.

Application:

A Quality Control Scenario

Scenario: A factory produces a specific type of electronic component. The company's quality control department knows that the probability of a randomly selected component being defective is 20% (or 0.20).

The team leader needs to find 5 non-defective components for a crucial product assembly. She wants to know the probability of having to inspect a certain number of components before she finds the 5th non-defective one.

This is a perfect scenario for the negative binomial distribution, as we are looking for the probability of a certain number of failures (defective components) before a fixed number of successes (non-defective components) is achieved.

Key Variables for NEGBINOMDIST:

The NEGBINOMDIST function typically requires three inputs:

Number of Failures (x): The number of defective components found. This is the variable we are solving for.
Number of Successes (k): The fixed number of non-defective components we need to find. In this case, k = 5.
Probability of Success (p): The probability of a single trial being a success. A "success" in this context is finding a non-defective component. Since the probability of a defective component is 0.20, the probability of a non-defective component is 1−0.20=0.80. So, p = 0.80.

Applying the NEGBINOMDIST Function:

The function calculates the probability mass function (PMF), which gives the probability of getting exactly x failures before the k-th success. The formula would look something like this in a spreadsheet program:

NEGBINOMDIST(x, 5, 0.80)

Let's calculate the probability of having to inspect 0, 1, 2, 3, and 4 defective components before finding the 5th non-defective one.

Table of Probabilities

	Number of Defective Components (x)	Total Components Inspected (x + 5)	Probability NEGBINOMDIST(x, 5, 0.8)	Calculation of Probability
	A	B	C	D
1	0	5	0.32768	$NEGBINOMDIST (0, 5, 0.80) = (5 - 1 0 + 5 - 1) (0.80)^{5} (0.20)^{0} = (4 4) (0.80)^{5} (0.20)^{0} = 1 \times 0.32768 \times 1 = 0.32768$
2	1	6	0.32768	$NEGBINOMDIST (1, 5, 0.80) = (5 - 1 1 + 5 - 1) (0.80)^{5} (0.20)^{1} = (4 5) (0.80)^{5} (0.20)^{1} = 5 \times 0.32768 \times 0.2 = 0.32768$
3	2	7	0.196608	$NEGBINOMDIST (2, 5, 0.80) = (5 - 1 2 + 5 - 1) (0.80)^{5} (0.20)^{2} = (4 6) (0.80)^{5} (0.20)^{2} = 15 \times 0.32768 \times 0.04 = 0.196608$
4	3	8	0.0917504	$NEGBINOMDIST (3, 5, 0.80) = (5 - 1 3 + 5 - 1) (0.80)^{5} (0.20)^{3} = (4 7) (0.80)^{5} (0.20)^{3} = 35 \times 0.32768 \times 0.008 = 0.0917504$
5	4	9	0.03670016	$NEGBINOMDIST (4, 5, 0.80) = (5 - 1 4 + 5 - 1) (0.80)^{5} (0.20)^{4} = (4 8) (0.80)^{5} (0.20)^{4} = 70 \times 0.32768 \times 0.0016 = 0.03670016$

Interpretation:

The table shows the precise probability of finding a certain number of defective components before the 5th non-defective component is found.

There is a 32.77% probability that the team leader will find the 5th non-defective component after inspecting exactly 5 total items (0 defective ones).
There is also a 32.77% probability that she will have to inspect 6 total items (1 defective and 5 non-defective).
The probability of needing to inspect more and more components (finding more defective ones) decreases significantly. This makes intuitive sense, as the probability of a component being non-defective is high (0.80).

The NEGBINOMDIST function is a powerful tool for analyzing situations where we need to model the number of "failures" before a specific number of "successes" is reached.

This page is protected by Google reCAPTCHA. Privacy - Terms.

Built using Zapof