Instructions for Running a t-Test in Excel.doc

Running a t-Test in EXCELTM 2003Heres an example: You measure iron concentration in 3 separate water samples from a stream and 3 separate water samples from a lake. Youre null hypothesis is that the stream and the lake will have the same iron concentration. Your results from the stream are 1.0, 0.5 and 0.8 ppm. Your results from the lake are 0.7, 0.2 and 0.3. To determine if your results are significant you need to perform a statistical test. Your null hypothesis is usually the hypothesis that states "there is no difference. . .". Scientists always try to disprove the null hypothesis. So you will probably be comparing two treatments or a treatment versus a control. In statistics you usually compare the average or mean values.In this example we will be comparing the mean stream value with the mean lake value. The number of replicates in this example is three, because thats how many samples we have from each treatment. The means in this case will be different. But is that difference due to randomness or is it due to the fact that they come from different water sources? We will use a statistical test to tell us the answer.The test we will be using is called Students t-test (named after the statistician Student). It is used for experiments where you are comparing two treatments or a treatment vs. control. For experiments with more than two treatments a different statistical test would be used.When you run the t-test, EXCEL will provide a printout that contains the mean values and other information. The most important part of these results is the p-value. The p-value tells you in an unbiased manner whether you must accept or reject the null hypothesis.Procedure1. Open a blank spreadsheet in EXCEL.2. Go to the cell in the upper left corner (cell A1). Type in the name of one of your treatments. Move one cell to the right (B1) and type in the name of the other treatment.3. Type in your data in column format beneath your two headers (see below).AB1StreamLake21.00.730.50.240.80.35Figure 1. An example of data correctly entered into an EXCEL spreadsheet in preparation for a two-sample t-test. Values represent the iron concentrations in each water sample in ppm.4. Click on Tools in the menu bar. If you see the option Data Analysis at the bottom of the drag down menu then skip to step 3. If not, then select Tools/AddIns. Check Analysis Toolpak and then click on OK.5. Click on Tools/Data Analysis. Scroll down and select t-test: Two Sample Assuming Equal Variances. A dialog box will open.6. Variable1 refers to one of your treatments (or columns). Click on the tiny spreadsheet icon next to the blank box for Variable 1. The box will disappear so that you can highlight your first column (include the treatment name as well as the data). Click again on the tiny spreadsheet icon.7. Repeat step six for Variable 2. 8. Fill in the following values: Hypothesized Mean Difference = 0; Alpha = 0.05; Labels = checked.9. Select OK. Examine the printout. First double check your values to make sure you didnt make a mistake. For example, the number of samples should equal the number of samples you typed in. 10. Record the mean and variance of your two treatments. Then look at the value for P(T<=t)two-tail and record it (see below).If the this p-value is less than 0.05, then you must reject the null hypothesis.If the this p-value is greater than 0.05, then you must accept the null hypothesis.t-Test: Two-Sample Assuming Equal VariancesRecord these numbers!StreamLakeMean0.4820.752Variance1.1251.691667Observations33Pooled Variance41.58333Hypothesized Mean Difference0df6t Stat6.908216P(T<=t) one-tail0.000227Record this P-valuet Critical one-tail1.943181P(T<=t) two-tail0.000455t Critical two-tail2.446914Figure 2. An example of the output from a two sample t-test in EXCEL.