One-Way Analysis of Variance With Excel
In the real world, you will probably never conduct analysis of variance by hand. Most likely, you will use computer software (SAS, SPSS, Minitab, Excel, etc.). In this lesson, we demonstrate how to conduct a one-way analysis of variance with Excel. And we explain how to interpret the results of our analysis.
Note: If you're curious about what goes on "behind the scenes" with Excel, read the previous lesson: One-Way Analysis of Variance: Example. That lesson shows the hand computations required to solve the same problem that we will solve in this lesson with Excel.
The Analysis ToolPak
To access the analysis of variance functions in Excel, you need a free Microsoft add-in called the Analysis ToolPak, which may or may not be already installed on your copy of Excel.
To determine whether you have the Analysis ToolPak, click the Data tab in the main Excel menu. If you see Data Analysis in the Analysis section, you're good. You have the ToolPak.
If you don't have the ToolPak, you need to get it. Go to: How to Install the Data Analysis ToolPak in Excel.
Problem Statement
To demonstrate how to conduct a one-way analysis of variance with Excel, we'll work through a real-world problem. Here's the problem:
A pharmaceutical company conducts an experiment to test the effect of a new cholesterol medication. The company selects 15 subjects randomly from a larger population. Each subject is randomly assigned to one of three treatment groups. Within each treament group, subjects receive a different dose of the new medication. In Group 1, subjects receive 0 mg/day; in Group 2, 50 mg/day; and in Group 3, 100 mg/day.
After 30 days, doctors measure the cholesterol level of each subject. The results for all 15 subjects appear in the table below:
| Dosage | ||
|---|---|---|
| Group 1, 0 mg |
Group 2, 50 mg |
Group 3, 100 mg |
| 210 | 210 | 180 |
| 240 | 240 | 210 |
| 270 | 240 | 210 |
| 270 | 270 | 210 |
| 300 | 270 | 240 |
In conducting this experiment, the experimenter has two research questions:
- Does dosage level have a statistically significant effect on cholesterol level?
- How strong is the effect of dosage level on cholesterol level?
One-Way ANOVA With Excel
When you conduct a one-way analysis of variance with Excel, the main output is an ANOVA summary table. As we've seen in previous lessons, an ANOVA summary table holds all the information we need to answer the research questions posed above.
Here is a step-by-step guide for producing an ANOVA summary table with Excel:
- Step 1. Enter data in rows or columns - one row or column per group. We'll
enter data in columns, as shown below:
- Step 2. From Excel's main navigation menu, click Data / Data Analysis
to display the Data Analysis dialog box.
- Step 3. In the Data Analysis dialog box, select "Anova: Single Factor" and click the
OK button to display the Anova: Single Factor dialog box.
- Step 4. In the Anova: Single Factor dialog box, enter the input range. Select "Columns" or "Rows",
depending on whether you entered data in columns or rows. Indicate whether you included labels in the
first column or row of the input range. And finally, enter a value for Alpha, the significance level.
For this exercise, we'll use a significance level of 0.05, as shown below:
- Step 5. From the Anova: Single Factor dialog box, click the OK button to
display the ANOVA summary table.
Interpretation of Results
Recall that the researchers undertook this study to answer two questions:
- Does dosage level have a statistically significant effect on cholesterol level?
- How strong is the effect of dosage level on cholesterol level?
Answers to both questions can be found in the ANOVA summary table.
Statistical Significance
A one-way analysis of variance tests the null hypothesis that the independent variable (dosage level) had no effect on the dependent variable (cholesterol level).
The P-value (shown in the last column of the ANOVA table) is the probability that an F statistic would be more extreme (bigger) than the F ratio shown in the table, assuming the null hypothesis is true. When the P-value is bigger than the significance level, we accept the null hypothesis; when it is smaller, we reject it. Here, the P-value (0.042) is smaller than the significance level (0.05), so we reject the null hypothesis and conclude that dosage level had a statistically significant effect on cholesterol level.
Magnitude of Effect
To assess the strength of the treatment effect, an experimenter might compute eta squared (η2). The computation is easy, using sum of squares entries from the ANOVA table, as shown below:
η2 = SSB / SST = 6,240 / 15,240 = 0.41
where SSB is the between-groups sum of squares and SST is the total sum of squares.
For this experiment, an eta squared of 0.41 means that 41% of the variance in the dependent variable can be explained by the effect of the independent variable.