Table of Contents
To perform a Bonferroni Correction in Excel, follow these steps:
1. Identify the significance level (alpha) for your hypothesis test.
2. Determine the number of comparisons being made in your data set.
3. Divide the significance level by the number of comparisons to obtain the adjusted alpha value.
4. Enter the adjusted alpha value into a new column in your Excel spreadsheet.
5. Use the “COUNTIF” function to count the number of observations that are greater than or equal to the adjusted alpha value.
6. Multiply the result by 2 to obtain the Bonferroni-adjusted p-value.
7. Compare the Bonferroni-adjusted p-value to the original alpha value to determine if the null hypothesis should be rejected or not.
8. Repeat these steps for each hypothesis test in your data set.
Performing a Bonferroni Correction in Excel allows for a more precise control of the Type I error rate when conducting multiple statistical tests, reducing the likelihood of falsely rejecting the null hypothesis.
Perform a Bonferroni Correction in Excel
A Bonferroni Correction refers to the process of adjusting the alpha (α) level for a family of statistical tests so that we control for the probability of committing a type I error.
The formula for a Bonferroni Correction is as follows:
αnew = αoriginal / n
where:
- αoriginal: The original α level
- n: The total number of comparisons or tests being performed
For example, if we perform three statistical tests at once and wish to use α = .05 for each test, the Bonferroni Correction tell us that we should use αnew = .01667.
αnew = αoriginal / n = .05 / 3 = .01667
Thus, we should only reject the null hypothesis of each individual test if the p-value of the test is less than .01667.
This type of correction is often made in following an ANOVA when we want to compare several group means at once.
The following step-by-step example shows how to perform a Bonferroni Correction following a one-way ANOVA in Excel.
Step 1: Create the Data
First, let’s create a fake dataset that shows the exam scores of students who used one of three different studying techniques to prepare for the exam:
Step 2: Perform the One-Way ANOVA
Next, let’s perform a one-way ANOVA to determine if the mean exam scores are equal across all three groups.
First, highlight all of the data including the column headers:
If you don’t see this option available, you need to first .
In the window that appears, click Anova: Single Factor and then click OK.
Fill in the following information, then click OK:
The results of the one-way ANOVA will automatically appear:
Recall that a one-way ANOVA has the following null and alternative hypotheses:
- H0 (null hypothesis): All group means are equal.
- HA (alternative hypothesis): At least one group mean is different from the rest.
Since the p-value in the ANOVA table (0.001652) is less than .05, we have sufficient evidence to reject the null hypothesis. In other words, the mean exam scores between the three groups are not equal.
Next, we can perform multiple comparisons using a Bonferroni correction between the three groups to see exactly which group means are different.
Step 3: Perform Multiple Comparisons Using a Bonferroni Correction
Using a Bonferroni correction, we can calculate the adjusted alpha level as follows:
αnew = αoriginal / n
In our example, we’ll be performing the following three comparisons:
- Technique 1 vs. Technique 2
- Technique 1 vs. Technique 3
- Technique 2 vs. Technique 3
Since we want to use α = .05 for each test, the Bonferroni Correction tell us that we should use αnew = .0167.
Next, we’ll use a t-test to compare the means between each group. In Excel, we can use the following syntax:
=TTEST(Array1, Array2, tails=2, type=2)
where:
- Array1: The first array of data
- Array2: The second array of data
- tails: The number of tails of the test. We’ll use “2” to indicate a two-tailed test.
- type: The type of t-test to perform. We’ll use “2” to indicate a t-test with equal variances.
The following screenshot shows how to perform each t-test:
The only p-value that is less than the Bonferroni-adjusted alpha level is from the comparison between technique 1 vs. technique 2, which had a p-value of 0.001042.
Thus, we would conclude that only statistically significant difference in mean exam scores was between technique 1 and technique 2.