You may obtain incorrect results and misleading labels when you use the Analysis ToolPak t-Test in Excel

This article describes the Analysis ToolPak t-Test that is named the Paired Two Sample for Means tool. This tool has not been changed for Microsoft Office Excel 2003 and for later versions of Excel. However, this tool gives flawed results when there is missing data. Additionally, whether or not there is missing data, there are misleading labels in the output of the tool.

Microsoft Excel 2004 for Macintosh information

The statistical functions in Excel 2004 for Mac were updated by using the same algorithms that were used to update the statistical functions in Excel 2003 and in later versions of Excel. Any information in this article that describes how a function works or how a function was modified for Excel 2003 and for later versions of Excel also applies to Excel 2004 for Mac.

Typically, you apply the Paired Two Sample t-Test (sometimes named the Matched Pairs t-Test) when you have Before and After measurements on the same experiments for a treatment. For example, you might measure a subject's weight before and after a 30-day diet plan.

Typically, you get rid of data on any subject whose Before measurement or After measurement is missing. Incomplete data on a subject makes information about that subject useless. Unfortunately, this Analysis ToolPak tool behaves differently than the typical practice. First, this Analysis ToolPak tool counts the number of subjects with Before measurements and the number of subjects with After measurements. If these totals are different, you receive an error message and this Analysis ToolPak tool does not continue. Therefore, for example, if there are 49 subjects who all have both Before and After measurements and a fiftieth subject who has only a Before measurement, the Analysis ToolPak tool does not do the analysis.

If the number of subjects that are missing Before data equals the number of subjects that are missing After data and this number is positive, the tool performs an inappropriate analysis. For example, assume that there are 50 subjects. Subject A is missing a Before measurement, and Subject B is missing an After measurement, and the other 48 subjects have no missing data. The tool counts 49 Before measurements and 49 After measurements; the tool acts as if there were 49 subjects with no missing data. This violates your intent of eliminating any subject who is missing a Before value or an After value. The number of subjects in this example should be 48, not 49. Therefore, the tool uses an incorrect number of degrees of freedom. Additionally, because the tool discards neither Subject A's After measurement nor Subject B's Before measurement, these two measurements are included in calculations of sample means that are used in the t-statistic. Therefore, these calculated sample means are inappropriate.

In summary, it is inappropriate to use the tool when there is missing data, because the tool either will not compute or it will compute with inappropriate formulas. The latter case occurs when the number of subjects with missing Before data equals the number of subjects with missing After data.

The example in the "Example of usage" section of this article illustrates these problems and also points out confusing labels in the tool's output. The "Workaround" section of this article suggests a workaround in a case where you cannot verify the absence of missing data before you use the tool.

Example of usage

To illustrate the problem of missing data, create a blank Excel worksheet, and then copy the following table. Select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:I52 in your worksheet.After the table is pasted into your Excel worksheet, click the Paste Options button, and then click Match Destination Formatting. With the pasted range still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:

• In Microsoft Office Excel 2007, click the Home tab, click Format in the Cells group, and then click AutoFit Column Width.
• In Excel 2003, point to Column on the Format menu, and then click AutoFit Selection.

You can use the worksheet to compare results of the TTEST function of Excel with the Analysis ToolPak tool. Experiment 1 shows complete data on eleven subjects. The value of TTEST in cell A16 is the probability that the t-statistic will be greater than the observed value, assuming a t-distribution with ten degrees of freedom. This value, 0.837, is also shown in the tool's output in cell B32. The label in cell A32 should read "P(T >= |t|) two-tail", not "P(T<=t) two-tail", but the numeric answer is correct because there is no missing data in Experiment 1. Similarly, the label in cell A30 should read "P(T >= |t|) one-tail", not "P(T<=t) one-tail". The "t Critical" cutoffs are correct. They correspond to the default significance level, 0.05, and they use the correct number of degrees of freedom, 10.

Experiment 2 has one missing After measurement on one subject and no other missing data. The tool refuses to compute. The values of TTEST in cells A16 and A17 are the same. In cell A16, the data cell range C3:D13 is used; this includes the last subject, the only one with missing data. In cell A17, the data cell range C3:D12 is used; this corresponds to an experiment with the first ten subjects and no missing data. The fact that the results are the same indicates that when TTEST is called in cell A16, TTEST appropriately discards the subject with missing data.

Experiment 3 has one missing Before measurement and one missing After measurement on two different subjects. Experiment 3 modified shows the nine remaining subjects with no missing data. The TTEST results in cells E16 and E17 are the same. In cell E16, TTEST is called on the Experiment 3 data in cells E3:F13. In cell E17, TTEST is called on the Experiment 3 modified data in cells G3:H11. The results are the same because TTEST appropriately discards the seventh and eleventh subjects in Experiment 3, the two with missing data. If you examine the tool's output for Experiment 3, the number of Before and After observations in cells B44 and C44 is ten in each case. It is easy to verify that SUM(E3:E13) is 1510 and SUM(F3:F13) is 1485; because there are 10 observations in each range, the respective means are 151 and 148.5, shown in cells B42 and C42. Therefore, the tool has not discarded any subjects and has included the After measurement for the seventh subject and the Before measurement for the eleventh subject in its analysis. The number of degrees of freedom in cell B47 is inappropriate, because there should have been nine subjects and eight df. This makes for incorrect entries of cutoff values in cells B50 and B52 (in addition to misleading labels for those entries in cells A50 and A52.)

Results in all versions of Excel

Unfortunately, this tool was not corrected for Excel 2003 and for later versions of Excel.

Suggested workaround for all versions of Excel

You can get rid of subjects with missing data before you use the tool. However, you may not want to edit an Excel worksheet in this way. One procedure for eliminating subjects with missing data is shown in the following steps. To get rid of subjects with missing data, follow these steps:

1. Copy the two data ranges to a new area of your worksheet.
2. Scan the data upward from the common bottom of the two ranges.
   1. If the bottom row contains missing data, clear the bottom row. This reduces the range of data. Go to step 3.
   2. Identify row r above the bottom row, but closest to the bottom with missing data.
      1. Copy all data below row r.
      2. Select row r, and then paste copied data into it.
      3. Clear the bottom row of data (which will now duplicate the next to last row of data). This reduces the range of data.
3. Repeat step 2 until no missing data remains.

Note You can use the tool if you can guarantee that there are no missing observations.

You can duplicate much but not all of the tool's output without transforming the data. You cannot find appropriate values for Mean, Variance, and Observations without a lot of effort. The tool finds inappropriate values by examining the Before and After data separately. The tool's df is the common value of Observations minus one; therefore, it is also inappropriate if there are missing data. You cannot find t Stat without a lot of effort, because you have to examine Before and After data at the same time.

However, you can find Pearson Correlation by applying PEARSON or CORREL to the two data ranges. Both of these Excel functions handle missing data appropriately. Also, you can find the one-tail and two-tail t probabilities associated with the data by calling the TTEST function of Excel, which handles missing data appropriately. For the one-tailed and two-tailed probabilities in experiment 3, you might call TTEST(E3:E13, F3:F13, 1, 1) and TTEST(E3:E13, F3:F13, 2, 1) respectively. You could also verify that results of these functions agree with those of the tool in Experiment 1, where the tool behaves appropriately because there are no missing data. The corresponding calls for Experiment 1 are TTEST(A3:A13, B3:B13, 1, 1) and TTEST(A3:A13, B3:B13, 2, 1) respectively.

For the critical cutoffs, you must establish the number of degrees of freedom. In experiments 1, 2, and 3, the correct numbers of degrees of freedom are ten, nine, and eight respectively. These numbers are always one less than the number of useful subjects in your data without missing Before or After measurements. For experiment 3, for example, you could enter in cell J3, =IF(OR(ISBLANK(E3), ISBLANK(F3)), 0, 1), then fill down this formula into cells J4:J13 and find df by entering in cell J14: =SUM(J3:J13) – 1.

After you establish df, you can use the TINV function of Excel. With significance level 0.05, the calls for Experiments 1, 2, and 3 would be TINV(0.05, 10), TINV(0.05, 9), and TINV(0.05, 8) respectively. These would return the "t Critical two-tail" values. To get the "t Critical one-tail values", you would use the analogous calls with the significance level doubled, such as TINV(0.10, 10), TINV(0.10, 9), and TINV(0.10, 8) respectively.

Conclusions

Do not use the Analysis ToolPak t-Test Paired Two Sample for Means tool unless you can make sure that there are no missing data points. This article describes suggestions for duplicating most of the tool's functionality by using Excel functions, instead of the Analysis ToolPak.

The tool also provides misleading "P(T<=t)" labels. This article describes the correct interpretations.