Syntax. BINOM.INV(trials;probability_s;alpha)
Definition. This function returns the smallest value for which the cumulative binomial distribution is greater than or equal to the specified probability. Use this function for quality assurance applications. For example, use the BINOM.INV() function to determine the largest number of defective parts that are allowed to come off an assembly line run without rejecting the entire lot.
Arguments
trials (required). The number of Bernoulli trials
probability_s (required). The probability for the success of each trial
alpha(required). The cumulative binomial probability
Note
If one of the arguments isn’t a numeric value, the BINOM.INV() function returns the #VALUE! error.
If trials isn’t an integer, the decimal places are truncated. If trials is less than 0, the BINOM.INV() function returns the #NUM! error.
If probability_s is less than 0 or greater than 1, the BINOM.INV() function returns the #NUM! error.
If alpha is less than 0 or greater than 1, the BINOM.INV() function returns the #NUM! error.
Background. The BINOM.INV() function returns the smallest value for a cumulative binomial distribution not exceeding the specified probability.
BINOM.INV() calculates how often an event can occur based on the probability p in a sample with n repetitions before its cumulative probabilities have a value greater than or equal to the cumulative binomial probability alpha.
This function can only be used for binomial distributions. Therefore, the events have to be independent and can return only two results: an event either occurs or doesn’t occur.
BINOM.INV() is the inverse function of BINOM.DIST().
See Also
You will find more information about binomial distributions in the section that discusses the BINOM.DIST()/BINOMDIST() function.
Example. We use the example from the BINOM.DIST() function to explain the BINOM. INV() function. Assume that you are on vacation in a foreign city and ask 100 people (n trial) for directions. This question can be answered only with yes or no. This means there is a probability of 50 percent that the answer is yes. Therefore, p is 0.5.
You use the BINOM.DIST() function to calculate the probability that 66 of the 100 respondents (2/3) answer yes. Then you calculate with BINOM.INV() how often the answer is yes based on the probability p = 0.5 in a sample with n = 100 repetitions before the cumulative probability has a value greater than or equal to the criteria alpha. Figure 12-18 shows the result.
Based on the probability of 0.1 percent, a maximum of 35 people should answer yes before the cumulative probability has a value greater than or equal to the probability criteria alpha.
The BINOM.INV() function calculates the maximum number of characteristics within the sample based on the given probability criteria alpha.