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

The Excel spreadsheet program has a tool to calculate the independent 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: Job satisfaction as a function of work schedule was investigated in two different factories. In the first factory the employees are on a fixed shift system while in the second factory the workers have a rotating shift system. Under the fixed shift system, a worker always works the same shift, while under the rotating shift system, a worker rotates through the three shifts. Using the scores below determine if there is a significant difference in job satisfaction between the two groups of workers.

The first step in solving this problem is to enter the work satisfaction scores for the two groups 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: Two-Sample Assuming Equal Variances. Complete the t-Test: Two-Sample Assuming Equal VAriances window as follows:

1. Enter $A$3:$A$14 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 B3 through B14). Note that we have included the label, Fixed Shift, in the range of cells we selected.
2. Enter $B$3:$B$14 in the Variable 2 Range: box (or click on the box and select the cells B3 through B14).
3. Enter 0 in the Hypothesized mean difference: box.
4. Click the Labels box so that we indicate we are using labels (Fixed Shift and Rotating Shift)
5. Under Output Options click the button for Output range: and enter $A$16 in the Output range: box (or click in the box and then click on the cell A16 to cause it to appear in the box).
6. Click OK.

Your spreadsheet should now appear as follows:

The results of the t-test can be seen in the resultant table. The value of t (t Stat)l is 2.210169858, which we can round off to 2.210 which is almost identical to the answer (2.209) we got when we calculated t without using the spreadsheet program. The small difference between the two answers is because of rounding the partial answers off during our calculations.

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

We can also read the critical value or cut-off value for t from the table by looking at t Critical two-tail which is 2.085962478, which is the same value (2.086) 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.210
   df = n₁ + n₂ - 2 = 11 + 11 - 2 = 20
4. Write the decision rule for rejecting the null hypothesis.
   Reject H₀ if t is >= 2.086 or if t <= -2.086
5. Write a summary statement based on the decision.
   Reject H₀, p < .05, two-tailed
6. Write a statement of results in standard English.
   There is a significant difference in job satisfaction betwen the two groups of workers.

We can see that the Excel spreadsheet program gives us an easy way to calculate the independent t value. It also provides us with the critical values of t for the alpha levels we specify (the default value is an alpha of .05) and the degrees of freedom (df) for the statistic.