The paired samples t-test can be used to test for a significant difference in measurements across observations from one time period to another. This post will demonstrate how to conduct the test using real-life data in Excel.
Loading the Analysis ToolPak
The first step is to locate the Analysis ToolPak in your workbook. Navigate to the Data tab on the ribbon. Do you see something toward the end called Data Analysis?
If yes, great! If not, no worries: you can have it loaded in a few simple steps. Please note that the ToolPak is not available for every version of Excel.
Introducing the data
To run the t-test, we will use real-life data published in the journal PLOS ONE. The data measures lung volume measurements across three groups of patients at two points in time.
Along with this raw data, the journal article includes the results of their statistical analysis which we will attempt to replicate now.
Framing the problem
Our objective is to find whether there is a statistically significant difference in the average lung volume between periods 1 and 2 for any of the patient groups. The paired sample t-test is the right test in this case. We will perform the test at the 95% confidence level using the ToolPak.
Conducting the analysis in Excel
With the ToolPak loaded, go to Data > Data Analysis > t-Test: Paired Two Sample for Means:
Select the input results
You will now be prompted to fill in an input menu. Start with the Group 1 data, and select Volume 1 for the first range and Volume 2 for the second. Make sure you check on labels in the input menu as well. You can place the results on the same sheet as the data for now.
Interpreting the results
Now let’s interpret the results. Because we are interested in either a positive or negative overall difference, we will use the two-tail test, checking for a p-value < .05 to indicate statistically significant results.
The p-value of >.75 in cell F15 of the output tells us the results are not statistically significant. This means there is no significant difference in means between periods 1 and 2.
Go ahead and try for the other two groups if you wish. I will include the solutions and interpretation in the completed exercise file.
Validating the results
Ideally, we will see the same results as the publication… and check it out, we do! The p-values foot exactly to what we found in Excel:
Ideally we should see the same results as the other thing
Nice work! You can compare your workbook to the solution file below:
Recap
The paired sample t-test is common in medical research to test scores before and after some intervention. You will also see it used in education and marketing in the same way. Keep in mind, however, that an observation must be measured at both periods 1 and 2 to be used in the test. This can make the paired samples t-test logistically challenging in many business settings, where polling customers once is difficult enough… let alone twice!
Many hospitals and schools, on the other hand, do not have this same problem with a more “captive” audience. Do you see where the paired-sample t-test could be of use to you? What other statistical tests would you like to see covered? Let me know in the comments.
Leave a Reply