[zz] Performing a t-test with Excel

from: http://eeb.bio.utk.edu/weltzin/GenEcol03/Stats/ttest.htm

1. Arrange your data so that each sample is in its own column, as in the following example:

Sample 1  Sample 2

7.85         12.50

8.51         12.94

13.66       6.26

11.03       6.10

6.59         13.19

8.04         10.74

14.16       6.06

8.13         12.53

6.79         15.45

11.06       15.64

5.83         15.19

10.73       14.93

6.68         7.94

5.02         8.28

10.37       12.65

2. It is always useful to look at a summary graph of your data. In this case, we can plot the mean value of the response variable for each sample. To do this with Excel, first calculate the means (by clicking at the “formula” icon in the toolbar, and selecting “average”) and the standard deviation (select “standard deviation” in the “formula” menu) of each of your samples. Then you can plot the mean for each sample using a bar graph. To to this, click on the “Chart wizard” icon in the toolbar, choose the “column” graph type, and click next. Select the means of the two samples as the data you want to plot); click next. You will have the option of adding a title, axes labels, etc.; do so if you wish, and next, and click finish in the last window. This will produce a graph like Figure 1 below.

Once you have plotted the means, you can add error bars with the standard deviation of your samples. This will give you an idea of the variability of your data. To do this, right-click once on one of the columns. This will open a menu; select “Format data series,” and go to the “Y error bars” window. Click the red arrows in the custom error amount (lower part of window); this will shrink the window. Select the cells where the standard deviations of your samples are, and click the red arrow again. You will have to this twice, for the + and – error bars. Click “OK.” This will modify your graph, as shown in Figure 2.

By looking at this graph, it is evident that the mean for sample 1 is lower than the mean for sample 2. Is this difference statistically significant? Well, the answer to that depends not only on the difference between the means of the two samples, but also on the difference between their variability. This is exactly what a t-test takes into account. (See an example of how variability of the data can determine the statistical significance of a t-test.)

3. To perform a t-test, go to the “Tools” menu, and select the “Data analysis” option; this will open the Analysis ToolPak. (If there is no “Data analysis” option in your “Tools” menu, then you have to install it; click here for help installing the Analysis ToolPak.) Select the two-sample t-test or the paired t-test option, as appropriate.

4. In the t-test window, select the ranges of each of your two variables. Select the significance level (alpha = 0.05 is the conventional value). In the “Output options” section select “New Worksheet Ply;” this will create a new page with your results. Click “OK.”

5. In the results page a table will be created with the following information:

t-Test: Two-Sample Assuming Equal Variances

Rows (1), (2), and (3) give you the mean, variance and number of observations for each variable. Row (4) gives you the “pooled” variance (i.e., for both samples together), used to calculate the t statistic. Row (5) gives the hypothesized mean difference (usually zero). Row (6) gives the “degrees of freedom.” Row (7) presents the t statistic (the highest the absolute value, the less similar the means of the two samples are). Row (8) gives you the one-tailed probability that the t statistic calculated for your data is lower than or equal to the critical t-value [given in row (9)]. Rows (10) and (11) give the probability and critical t-value for two tails. (You should use a one-tailed test if your hypothesis is that the mean of sample 1 is either higher or lower than the mean of sample 2; you should use a two-tailed test if your hypothesis is that the means of the two samples differ, no matter which one is higher and which is lower.)

So, in this example, the mean of sample 2 is higher than the mean of sample 1. Suppose our hypothesis was that the means are different, no matter which one is higher; we would then use the two-tailed test. This difference is statistically significant, since the two-tailed probability is 0.001587, which is much lower than alpha (i.e., 0.05).