BINOM.INV

Calculates the minimum number of successful trials required to achieve a target probability in a binomial distribution.

Syntax:

BINOM.INV(trials, probability_s, alpha)

trials is required, and the total number of trials.

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

alpha is required, and is the criterion value that is the target probability.

Example:

If trials contains 10, probability_s contains 0.5 and alpha contains 0.9:

BINOM.INV(10, 0.5, 0.9)

returns 7

This example finds the minimum number of heads you need to get in 10 flips to have at least a 90% chance of achieving that many heads or more.

Trials:

Probability_s:

Alpha:

Result:

7

Application:

Quality Control for a Widget Manufacturer

Imagine you are a quality control manager for a company that manufactures a specific type of widget. You know from historical data that the probability of any single widget being defective is 3%, or 0.03. A supervisor asks you to inspect a batch of 500 widgets and wants to know the minimum number of defective widgets you should expect to find with at least 95% certainty.

You can use the BINOM.INV function to answer this question.

The BINOM.INV function has the following arguments:

Trials: The number of independent trials. In this case, it's the total number of widgets you are inspecting.
Probability_s: The probability of success in a single trial. Here, a "success" is defined as finding a defective widget.
Alpha: The cumulative probability. This is the confidence level you want to achieve.

Here is how you would set up the problem in a spreadsheet:

	Label	Value	Description
	A	B	C
1	Number of Trials	500	Total number of widgets to inspect.
2	Probability of Success	0.03	The probability that a single widget is defective.
3	Cumulative Probability (Alpha)	0.95	The desired confidence level. We want to be 95% confident that the number of defects is at least this high.
4	Formula	BINOM.INV(B1, B2, B3)	The formula to calculate the result.
5	Result	22	The calculated minimum number of defective widgets.

Explanation of the Result:

When you enter the formula BINOM.INV(500, 0.03, 0.95) into a spreadsheet, the result is 22.

This means that out of 500 widgets, you can be 95% confident that you will find at least 22 defective widgets. The function finds the smallest integer (in this case, 22) for which the cumulative binomial probability of having 22 or more successes (defective widgets) is greater than or equal to 0.95.

To put it another way, the probability of finding 21 or fewer defective widgets is less than 5%, while the probability of finding 22 or fewer is greater than or equal to 95%. The BINOM.INV function gives you this critical threshold. This is a critical piece of information for the quality control manager, as it sets an expectation for the number of defects they should be prepared to handle

If they inspect the batch and find significantly fewer than 22 defective widgets, it might indicate a positive change in the manufacturing process. If they find significantly more, it could be a red flag that something is wrong with the production line.