Using the Excel Spreadsheet Program to Calculate the Dependent t-test

The Excel spreadsheet program has a tool to calculate the dependent t value 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: The Beck Depression Scale (pre-test) was administered to ten adolescents undergoing anger management thereapy. After four weeks of therapy the same scale was administered again (post-test). Does the anger management therapy significantly reduce the scores on the depression scale?

In this problem we are comparing pre-test and post-test scores for a group of subjects. This would be an appropriate situation for the dependent t-test.

The pre-test and post-test scores for the ten subjects are in the following table.

The first step in solving this problem is to enter the pre-test and post-test scores for the 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 t-Test: Paired Two Sample for Means. Complete the t-Test: Paired Two Sample for Means window as follows:

1. Enter $B$2:$B$12 in the Variable 1 Range: box (or you can enter that value automatically by clicking in the box and then selecting the range of cells B2 through B12). Note that we have included the label, Posttest, in the range of cells we selected. Also notice that we are putting the posttest scores (the second column in the table) in for variable 1 range. This is so that the data tool will subtract the pretest from the posttest as the calculations are completed.
2. Enter $A$2:$A$12 in the Variable 2 Range: box (or click on the box and select the cells A2 through A12).
3. Enter 0 in the Hypothesized mean difference: box.
4. Click the Labels box so that we indicate we are using labels (Pretest and Posttest)
5. Under Output Options click the button for Output range: and enter $A$14 in the Output range: box (or click in the box and then click on the cell A14 to cause it to appear in the box).
6. Click OK.

Your spreadsheet should now appear as follows:

The results of the dependent t-test can be seen in the resultant table. The value of t (t Stat) is -2.6234239, which we can round off to -2.623 which is identical to the answer we got when we calculated t without using the spreadsheet program.

The probability of this result being due to chance can be read from the table as 0.01382762 (see t Critical one-tail) which means that this result is significant at the .01 level. We will set our alpha level as .05, so we will say that p < .05 rather than that p = .01

We can also read the critical value or cut-off value for t from the table by looking at t Critical one-tail which is 1.83311386, which is the same value (1.833) we looked up in the table in the textbook when we were calculating t without using the spreadsheet.

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.
   
   
2. Set the alpha level.
   
3. Calculate the value of the appropriate statistic. Also indicate the degrees of freedom for the statistical test if necessary.
   t = -2.623
   df = n - 1 = 10 - 1 = 9
4. Write the decision rule for rejecting the null hypothesis.
   Reject H₀ if t is <= -1.833
5. Write a summary statement based on the decision.
   Reject H₀, p < .05, one-tailed
6. Write a statement of results in standard English.
   The management therapy did significantly reduce the depression scores for adolescents.

We can see that the Excel spreadsheet program gives us an easy way to calculate the dependent t value. It also provides us with the critical values of t for the alpha level we specified, and the degrees of freedom (df) for the statistic.