How to Perform a Bonferroni Correction in Excel?

The Bonferroni correction, also known as the Bonferroni type adjustment, is one of the most fundamental processes used in multiple comparison testing. The name was inspired by Carlo Emilio, the museum’s Italian curator. The procedure of altering the alpha (α) level for a series of statistical tests to account for the probability of making a type I error is known as a Bonferroni Correction. 

Bonferroni Correction Formula

The formula to calculate the Bonferroni Correction in Excel is given below,

α_new = α_original /n

• α_original: The initial α level.
• n: The total number of comparisons or tests carried out.

Example: If we run four statistical tests at the same time and want to use α=.05 for each, the Bonferroni Correction tells us that we should use α_new = .0125.

α_original / n = α_new

.05 / 4 = .0125

As a result, for each individual test, we should reject the null hypothesis only if the p-value is less than .0125. When comparing the means of many groups at the same time, this type of modification is widely utilized in ANOVA.

Steps to Perform a Bonferroni Correction in Excel

The following example shows how to do a Bonferroni Correction in Excel following a one-way ANOVA.

Step 1: First, we’ll enter each test’s data in the way shown below:

Step 2: Following that, we’ll perform a one-way ANOVA to examine if the mean exam results are consistent across all four groups. Begin by emphasizing all of the material, including the column headers:

Then, select the Data tab from the top ribbon, Under the Analysis group select the Data Analysis:

Step 3: In the box that appears, there is multiple Analysis Tool but Anova: Single Factor is selected here and then OK.

Now the Anova: Single Factor box appears fill their Input Range from $A$1: $D$11 and on Output Range, select any empty Cell. Here F1 is selected. Check the Label in the First Row column. Then click OK.

Step 4: The following one-way ANOVA: Single Factor results will be displayed. Here you can see the summary of Anova and the different Sources of variation.

Here p-value in the ANOVA table (0.008573) is less than.05., we have adequate evidence to reject the null hypothesis. In other words, the mean exam scores of the four groups are not similar. Using a Bonferroni correction, we can then conduct multiple comparisons across the four groups to check if group means differ.

Step 5: Using a Bonferroni correction, we can calculate the adjusted alpha level as follows:

α_new = α_original / n

In this example, we will do the three following comparisons:

• Test 1 vs. Test 2
• Test 1 vs. Test 3
• Test 1 vs. Test 4
• Test 2 vs. Test 3
• Test 2 vs. Test 4
• Test 3 vs. Test 4

Because we intend to use α=.05 for each test, the Bonferroni Correction suggests that we use α_new =.0125.

Step 6: A t-test will then be used to compare the means of each group. In Excel, we may use the following syntax:

=TTEST (Array1, Array2, tails=2, type=2)

• Array1: The initial data array.
• Array2: The second data array.
• Tail: The test’s number of tails. A two-tailed test will be denoted by “2.”
• Type: The t-test type to be used. “2” represents a t-test with equal variances.

The screenshot below explains how to execute each t-test:

The comparison of test 1 vs. test 4 yields only a p-value less than the Bonferroni-adjusted alpha threshold, with a p-value of 0.001136. As a consequence, we may infer that the only statistically significant difference in mean was discovered between tests 1 and 4.

Conclusion

This research revealed a substantial difference between all test means. Test of ANOVA analysis revealed a significant difference between at least two of the means. This Test study reveals that all treatment methods differ. One approach is to use Bonferroni’s technique. This strategy uniformly distributes the value among all pairs of treatment methods.