In Excel’s BINOM.DIST function, when cumulative is set to TRUE (or 1):

• Excel adds up all probabilities up to and including the number of successes ([latex]x[/latex]-value) inputted
• [latex]P(X \le x) = \text{BINOM.DIST}(x,n, p, 1)[/latex]

What if we want the values above a certain x-value instead?

• We need to use a complement when using Excel’s BINOM.DIST
• This is because [latex]P(X \gt x) = 1 − P(X\le x)[/latex]
• In Excel, this gives: [latex]P(X \gt x) = 1 − \text{BINOM.DIST}(x, n, p, 1)[/latex]

Using Formulas to Calculate More than (Example)

In Excel’s BINOM.DIST function, when cumulative is set to TRUE (or 1), Excel adds up all probabilities up to and including the number of successes ([latex]x[/latex]-value) inputted. What if we want the values above a certain [latex]x[/latex]-value instead?

Example 27.1 – Using the Formula

Problem Setup: A hotel’s records indicate that 65% of its guests are visitors from Canada.

Question: From a random sample of 12 guests, what is the probability that more than 10 of them are from Canada?

Solution: We know the following:

• [latex]P(\text{more than 10}) = P(x>10) = P(x=11) + P(x=12)[/latex]
• [latex]n=12[/latex] and [latex]p=0.65[/latex].

This gives:

• [latex]P(x=11) = {}_{12}C_{11} \cdot (0.65)^{11} \cdot (1 - 0.65)^{12-11} = \frac{12!}{11!1!} \cdot (0.65)^{11} \cdot (0.35)^1 =12(0.00875)(0.35) = 0.03675[/latex]
• [latex]P(x=12) = {}_{12}C_{12} \cdot (0.65)^{12} \cdot (1 - 0.65)^{12-12} = \frac{12!}{12!0!} \cdot (0.65)^{12} \cdot (0.35)^0 =1(0.00569)(1) = 0.00569[/latex]
• [latex]P(x\gt10) =  P(x=11)+P(x=12) = 0.03675+ 0.00569 = 0.04244[/latex]

Conclusion: There is an 4.24% chance that more than 10 of them are from Canada.

Example 27.2 – Using the Formula and a Complement

Problem Setup: Let us now revisit example 27.1 but using a complement.

Question: Use a complement to calculate the probability of more than 10 guests being from Canada.

Solution: We know all of the possible outcomes need to add to 1:

\[ P(x=0)+P(x=1)+P(x=2)+P(x=3)+P(x=4)+P(x=5)+P(x=6)+P(x=7)+P(x=8)+P(x=9)+P(x=10)+P(x=11)+P(x=12) = 1 \]

We can use the above equation to solve for [latex]P(x\gt 10) = P(x=11) + P(x=12)[/latex]:

[latex]\begin{align*} P(x \gt 10) &= P(x=11)+P(x=12) \\ \\ &= 1-[P(x=0)+P(x=1)+P(x=2)+P(x=3)+P(x=4)+P(x=5)+P(x=6)+P(x=7)+P(x=8)+P(x=9)+P(x=10)] \\ \\ &= 1-[0.0000+0.0001+0.0008+0.0048+0.0199+0.0591+0.1281+0.2039+0.2367+0.1954+0.1089] \\ \\ &= 1-0.9576 = 0.0424 \end{align*}[/latex]

Conclusions:

• This is quite the calculation to perform!
• If you would like more practice – try to find the probabilities given above.
• It is recommended, however, that we use Excel for calculations like these!
• Click here to download the Excel sheet and also see the next section.

Using Excel to Calculate More than (Exercise)

• Excel’s BINOM.DIST() function can calculate the probability up to an x value.
• If we combine this with a complement, we can calculate the probability of more than a value.
• We will try this out in the next example.

Example 27.3 – Using Excel and a Complement

Problem Setup: Let us now revisit example 27.2 but we will use Excel.

Question: Use a complement and Excel’s BINOM.DIST to calculate the probability of more than 10 guests being from Canada when 12 guests are selected at random and the probability of any guest being from Canada is 65%.

Solution: We know that:

• P([latex]X[/latex] ≤ [latex]x[/latex]) = BINOM.DIST([latex]x[/latex], [latex]n[/latex], [latex]p[/latex], 1)
• P([latex]x[/latex] > 10) = 1 – P([latex]x[/latex] ≤ 10)

You try: Use the above to solve for P([latex]x[/latex] > 10) in the exercise below:

ViDeo of Using Excel to Calculate More than (VIDEO)

• Excel’s BINOM.DIST([latex]n[/latex]) function can calculate the probability up to an x value.
• We do this by setting cumulative = TRUE or 1.
• We will try this out in the next example.

Example 27.4 – Using Excel and a Complement

Problem Setup: Let us now revisit example 27.3 but let’s slightly change what we want to calculate.

Question: What is the probability of more than 2 guests NOT being from Canada when 12 guests are selected at random and the probability of any guest being from Canada is 65%? Use a complement and Excel’s BINOM.DIST.

Solutions: Click here to download the Excel solution. Also, see the video below:

Conclusion: There is an 84.87% chance that more than 2 of the 12 guests are not from Canada.

Key Takeaways (EXERCISE)

Your Own Notes (EXERCISE)

• Are there any notes you want to take from this section? Is there anything you’d like to copy and paste below?
• These notes are for you only (they will not be stored anywhere)
• Make sure to download them at the end to use as a reference