How to perform a Binomial Test in Excel?

---

A binomial test compares a sample proportion to a hypothesized proportion.

For example, suppose we have a 6-sided die. If we roll it 24 times, we would expect the number “3” to show up 1/6 of the time, e.g. 24 * (1/6) = 4 times.

If the number “3” actually shows up 6 times, is that evidence that the die is biased towards the number “3”? We could perform a binomial test to answer that question.

In Excel, we can use the following function to perform a binomial test:

BINOM.DIST(number_s, trials, probability_s, cumulative)

where:

• number_s: number of “successes”
• trials: total number of trials
• probability_s: the probability of success on each trial
• cumulative: If TRUE, then BINOM.DIST returns the cumulative distribution function, which is the probability that there are at most number_s successes; if FALSE, it returns the probability mass function, which is the probability that there are number_s successes. We will almost always use TRUE.

The following examples illustrate how to perform binomial tests in Excel.

Example 1: We roll a 6-sided die 24 times and it lands on the number “3” exactly 6 times. Perform a binomial test to determine if the die is biased towards the number “3.”

The null and alternative hypotheses for our test are as follows:

H₀: π ≤ 1/6 (the die is not biased towards the number “3”)

H_A: π > 1/6

*π is the symbol for population proportion.

We will enter the following formula into Excel:

P(x ≥ 6) = 1 – BINOM.DIST(5, 24, 1/6, TRUE) = 1 – 0.80047 = 0.19953.

Because this p-value is not less than 0.05, we fail to reject the null hypothesis. We do not have sufficient evidence to say the die is biased towards the number “3.”

Example 2: We flip a coin 30 times and it lands on heads exactly 19 times. Perform a binomial test to determine if the coin is biased towards heads.

H₀: π ≤ 1/2 (the coin is not biased towards heads)

H_A: π > 1/2

We will enter the following formula into Excel:

P(x ≥ 19) = 1 – BINOM.DIST(18, 30, 1/2, TRUE) = 1 – 0.89976 = 0.10024.

Because this p-value is not less than 0.05, we fail to reject the null hypothesis. We do not have sufficient evidence to say the coin is biased towards heads.

Example 3: A shop makes widgets with 80% effectiveness. They implement a new system that they hope will improve the rate of effectiveness. They randomly select 50 widgets from a recent production run and find that 46 of them are effective. Perform a binomial test to determine if the new system leads to higher effectiveness.

The null and alternative hypotheses for our test are as follows:

H₀: π ≤ 0.80 (the new system does not lead to an increase in effectiveness)

H_A: π > 0.80

We will enter the following formula into Excel:

P(x ≥ 46) = 1 – BINOM.DIST(45, 50, 0.8, TRUE) = 1 – 0.9815 = 0.0185.

Because this p-value is less than 0.05, we reject the null hypothesis. We have sufficient evidence to say the new system leads to an increase in effectiveness.

Example 4: A shop makes gadgets with 60% reliability. They implement a new process that they hope will improve the reliability. They randomly select 40 gadgets from a recent production run. What is the minimum number of gadgets that need to be reliable in order for the shop to say, with 95% confidence, that the new process improves the reliability?

For this example we will need to use the following function:

BINOM.INV(trials, probability_s, alpha)

where:

• trials: total number of trials
• probability_s: probability of “success” on each trial
• alpha: significance level

We will enter the following formula into Excel:

BINOM.INV(40, 0.60, 0.95) = 29.

Thus, we would need at least 29 of the gadgets to be reliable in order to say, with 95% confidence, that the new process improves reliability.