Using the Excel Spreadsheet Program to Calculate One-Way Analysis of Variance

The Excel spreadsheet program has a tool to calculate One-Way Analysis of Variance, which simplifies our computational task considerably. Let's use the same research problem we already considered, but use the spreadsheet program to do the calculations.

Research Problem:

Three groups of students, 5 in each group, were receiving therapy for severe test anxiety. Group 1 received 5 hours of therapy, group 2 - 10 hours and group 3 - 15 hours. At the end of therapy each subject completed an evaluation of test anxiety (the dependent variable in the study). Did the amount of therapy have an effect on the level of test anxiety?

In this problem we are comparing the differences among the means representing three levels of the independent variable (hours of therapy). This would be an appropriate situation for one-way analysis of variance.

The three groups of students received the following scores on the Test Anxiety Index (TAI) at the end of treatment.

The first step in solving this problem is to enter the TAI scores for the three groups of subjects into an Excel Worksheet. After we have done this our worksheet should look as follows:

In the Excel Worksheet select Data Analysis under the Tools menu. If Data Analysis is not available you must install the Data Analysis Tools.

If you need to you can install the data analysis tools as follows:

1. Select Add-Ins from the Tools menu.
2. In the Add-Ins window click on the box next to Analysis Tool Pak to select it.
3. Click OK. You have now installed the Tool Pak.

With the Data Analysis Tools installed, select Data Analysis under the Tools menu.

In the Data Analysis window scroll down and select Anova: Single Factor. Complete the Anova: Single Factor window as follows:

1. Enter $A$2:$C$7 in the Input Range: box (or you can enter that value automatically by clicking in the box and then selecting the range of cells A2 through C7). Note that we have included the labels, Group 1, Group 2, and Group 3, in the range of cells we selected.
2. Click the Columns button so that we indicate we our data is grouped by columns.
3. Click the Labels in first row box so that we indicate we are using labels (Group 1, Group 2, and Group 3)
4. Enter .05 in the Alpha: box.
5. Under Output Options click the button for Output range: and enter $A$9 in the Output range: box (or click in the box and then click on the cell A9 to cause it to appear in the box).
6. Click OK.

Your spreadsheet should now appear as follows:

The results of the one-way analysis of variance can be seen in the resultant tables. The means for the three groups (as well as the count, sum, and variance for each group) can be seen in the SUMMARYtable.

The ANOVA table shows the same results as we put in the Analysis of Variance table when we calculated the results ourselves. The value of F is shown to be 5.178082192, which rounded to 5.18 is the same value as we received when we calculated F. The P-Value is shown as .02391684 which indicates that the result is significant at the .02 level. We have set our alpha level as .05 so we will simply indicate that p < .05. There is an additional entry to the table showing the critical value of F at the .05 level (F Crit) which is 3.88529031 which is similar to the result (2.88) we looked up in Appendix Table D in the textbook.

Unfortunately, the spreadsheet program does not have a program to calculate the Scheffe test, so we will have too calculate those the way we did before. The results of our Scheffe tests were:

Summary of Scheffe Test Results

Group One versus Group Two 4.62
Group One versus Group Three 0.18
Group Two versus Group Three 2.96

We now have all the information we need to complete the six step statistical inference process:

1. State the null hypothesis and the alternative hypothesis based on your research question.
   
   
   Note: Our null hypothesis, for the F test, states that there are no differences among the three means. The alternate hypothesis states that there are significant differences among some or all of the individual means. An unequivocal way of stating this is not H₀.
2. Set the alpha level.
   
   Note: As usual we will set our alpha level at .05, we have 5 chances in 100 of making a type I error.
3. Calculate the value of the appropriate statistic. Also indicate the degrees of freedom for the statistical test if necessary and the results of any post hoc test, if they were conducted.
   F(2,12) = 5.178, value of the F ratio
   F_.05(2,12) = 3.88, critical value of F
   F₁₂ = 4.630, Scheffe test value for comparing means 1 and 2
   F₁₃ = 0.185, Scheffe test value for comparing means 1 and 3
   F₂₃ = 2.963, Scheffe test value for comparing means 2 and 3

4. Write the decision rule for rejecting the null hypothesis.
   Reject H₀ if F is >= 3.88
   Note: To write the decision rule we had to know the critical value for F, with an alpha level of .05, 2 degrees of freedom in the numerator (df between groups) and 12 degrees of freedom in the denominator (df within groups). We can do this by looking at Appendix Table D and noting the tabled value for the .05 level in the column for 2 df and the row for 12 df.
5. Write a summary statement based on the decision.
   Reject H₀, p < .05
   Note: Since our calculated value of F (5.178) is greater than 3.88, we reject the null hypothesis and accept the alternative hypothesis.
6. Write a statement of results in standard English.
   There is a significant difference among the scores the three groups of students received on the Test Anxiety Index.
   Group 1 (the five hour therapy group) has a significantly lower score on the TAI than does Group 2 (the ten hour therapy group).

We can see that the Excel spreadsheet program gives us an easy way to calculate the F ratio. It also provides us with an analysis of variance table which shows, among other things, the critical value of F for the alpha level we specified, and the probability level (p) of the result.