BINOM.DIST.RANGE

Calculates the probability of an outcome based on a binomial distribution.

Syntax:

BINOM.DIST.RANGE(trials, probability_s, number_s, number_s2)

trials is required, and is the total number of trials, that should be greater than or equal to 0.

probability_s is required, and is the probability of success on each individual trial that should be greater than or equal to 0, or less than or equal to 1.

number_s is required, and is the number of successes in trials that should be greater than or equal to 0, or less than or equal to trials.

number_s2 is optional. When provided, number_s2 represents the upper bound of the success range, and number_s will be used as the lower bound. It must be greater than or equal to number_s and less than or equal to the total number of trials.

Example:

If trials contains 10, probability_s contains 0.5, number_s contains 3, and number_s2 contains 5:

BINOM.DIST.RANGE(10, 0.5, 3, 5)

returns 0.568359375

This example finds the probability of getting between 3 and 5 heads, when you flip a coin 10 times.

Trials:

Probability_s:

Number_s:

Number_s2:

Result:

0.568359375

Application:

Quality Control in a Manufacturing Plant

Scenario:

A factory produces a certain type of electronic component. The quality control department has determined that historically, the probability of a single component being defective is 5% (p = 0.05). A quality control inspector randomly selects a batch of 20 components (n = 20) for testing.

The inspector wants to calculate the probability of finding a certain number of defective components within this batch. This is a classic binomial distribution problem because:

There's a fixed number of trials (n = 20 components).
Each trial is independent (one component being defective doesn't affect another).
There are only two possible outcomes for each trial (defective or not defective).
The probability of success (finding a defective component) is constant (p = 0.05).

The Question:

What is the probability that the inspector finds a number of defective components between 1 and 3 (inclusive) in the batch of 20?

Using the BINOM.DIST.RANGE Function:

The BINOM.DIST.RANGE function is perfect for this as it calculates the cumulative probability for a range of successes.

The function syntax is: BINOM.DIST.RANGE(trials, probability_s, number_s, [number_s2])

trials (n): The number of independent trials. In our case, this is 20.
probability_s (p): The probability of success on each trial. Here, "success" is finding a defective component, so it's 0.05.
number_s: The lower limit of the number of successes. We want to find the probability of finding 1 or more defective components. So, this is 1.
[number_s2] (optional): The upper limit of the number of successes. We want to find the probability up to 3 defective components. So, this is 3.

Formula in a Spreadsheet:

BINOM.DIST.RANGE(20, 0.05, 1, 3)

Explanation of the Calculation:

This function essentially performs the following calculation: P(X = 1) + P(X = 2) + P(X = 3) where X is the number of defective components.

The Result:

The function will return the precise value of 0.6256125516.

This means there is a 62.56% probability that the inspector will find between 1 and 3 defective components (inclusive) in the batch of 20.

Table of Probabilities for BINOM.DIST.RANGE

To better understand what the function is doing, we can look at the individual probabilities that are being summed. The table below shows the probability of finding exactly k defective components, for k from 0 to 5.

	Number of Defective Components (k)	Probability (P(x=k))	Calculation using BINOM.DIST
	A	B	C
1	0	0.3585	BINOM.DIST(0, 20, 0.05, FALSE)
2	1	0.3774	BINOM.DIST(1, 20, 0.05, FALSE)
3	2	0.1887	BINOM.DIST(2, 20, 0.05, FALSE)
4	3	0.0596	BINOM.DIST(3, 20, 0.05, FALSE)
5	4	0.0133	BINOM.DIST(4, 20, 0.05, FALSE)
6	5	0.0022	BINOM.DIST(5, 20, 0.05, FALSE)

How the BINOM.DIST.RANGE result is derived from this table:

The BINOM.DIST.RANGE(20, 0.05, 1, 3) function adds up the probabilities for the specified range:

$P (1 \leq X \leq 3) = P (X = 1) + P (X = 2) + P (X = 3)$

$P (1 \leq X \leq 3) = 0.3774 + 0.1887 + 0.0596$

$P (1 \leq X \leq 3) \approx 0.6256$

In conclusion, BINOM.DIST.RANGE is a powerful tool for quickly calculating cumulative binomial probabilities over a specified range, saving you the work of adding up individual probabilities from the BINOM.DIST function.