NEGBINOM.DIST

Calculates the probability of a given number of failures before a specified number of successes occur in a series of independent trials.

Syntax:

NEGBINOM.DIST(number_f, number_s, probability_s, cumulative)

number_f is required, and is the number of failures.

number_s is required, and is the number of successes.

probability_s is required, and is the probability of success on each trial.

cumulative is required, and is a logical value:

TRUE: Returns the cumulative distribution function (the probability of getting at most number_f failures before number_s successes).

FALSE: Returns the probability mass function (the probability of getting exactly number_f failures before number_s successes).

Example:

If number_f contains 3, number_s contains 5, probability_s contains 0.5 and cumulative contains FALSE:

NEGBINOM.DIST(3, 5, 0.5, FALSE)

returns 0.13671875

This example finds the probability of getting exactly 3 tails (failures) before you get 5 heads (successes), assuming the coin is fair (probability of heads = 0.5).

number_f:

number_s:

probability_s:

Cumulative:

Result:

0.13671875

Application:

Basketball Free Throws

Imagine a basketball player, Alex, who is a very consistent free-throw shooter. Alex has a 75% success rate for making a free throw. We want to find the probability that Alex will miss exactly two shots before making five successful free throws.

Here's how we can use the NEGBINOM.DIST function to solve this problem:

Number of failures (k): We want to find the probability of exactly 2 misses.
Number of successes (r): We are looking for the probability before 5 successful shots are made.
Probability of success (p): Alex's success rate is 75%, or 0.75.
Cumulative: This is a boolean value. We will use FALSE because we want the exact probability of 2 failures, not the cumulative probability of 2 or fewer failures.

So, the formula would be: NEGBINOM.DIST(2, 5, 0.75, FALSE)

Let's break down the calculation:

	Parameter	Value	Description
	A	B	C
1	Number of failures	2	The number of missed free throws (failures) we are interested in.
2	Number of successes	5	The target number of successful free throws.
3	Probability of success	0.75	The probability of making a free throw on any given attempt.
4	Cumulative	FALSE	We are looking for the exact probability of 2 failures, not the cumulative probability.

By plugging these values into the function, we find that the probability of Alex missing exactly two shots before making five successful free throws is approximately 22.25%.

Result for NEGBINOM.DIST(2, 5, 0.75, FALSE):

0.2225