## Wednesday, May 28, 2014

### Paired t-Test in 4 Steps in Excel 2010 and Excel 2013

This is one of the following seven articles on Paired (2-Sample Dependent) t-Tests in Excel

Paired t-Test in 4 Steps in Excel 2010 and Excel 2013

Excel Normality Testing of Paired t-Test Data

Paired t-Test Excel Calculations, Formulas, and Tools

Paired t-Test – Effect Size in Excel 2010, and Excel 2013

Paired t-Test – Test Power With G-Power Utility

Wilcoxon Signed-Rank Test in 8 Steps As a Paired t-Test Alternative

Sign Test in Excel As A Paired t-Test Alternative

# Paired t-Test in 4 Steps in Excel

This hypothesis test determines whether the mean of a sample of differences between pairs of data (x_bardiff) is equal to (two-tailed test) or else greater than or less than (one-tailed test) than a constant. Before-and-after fitness levels of individuals undergoing a training program would be an example of paired data. The sample evaluated would be the group of differences between the before-and-after scores of the individuals. This is called the difference sample.

x_bardiff = Observed Difference Sample Mean  df = n – 1

Null Hypothesis H0: x_bardiff = Constant

The Null Hypothesis is rejected if any of the following equivalent conditions are shown to exist:

1) The observed x_bardiff is beyond the Critical Value.

2) The t Value (the Test Statistic) is farther from zero than the Critical t Value.

3) The p value is smaller than α for a one-tailed test or α/2 for a two-tailed test.

## Example of Paired, 1-Tailed t-Test in Excel

This problem is very similar to the problem solved in the z-test section for a paired, one-tailed z-test. Similar problems were used in each of these sections to show the similarities and also contrast the differences between the paired z-Test and t-test as easily as possible.

A new clerical program was introduced to a large company with the hope that clerical errors would be reduced. 5,000 clerical workers in the company underwent the training program. 17 Clerical employees who underwent the training were randomly selected. The average number of clerical errors that each of these 17 employees made per month for six months prior to the training and also for six months following the training were recorded. Each of the 17 employees had a similar degree of clerical experience within the company and performed nearly the same volume and type of clerical work in the before and after months.

Based upon the results of the 17 sampled clerical employees, determine with 95 percent certainty whether the average number of monthly clerical mistakes was reduced for the entire 5,000 clerical employees who underwent the training.

It is the difference that we are concerned with. A hypothesis test will be performed on the sample of differences. The distributed variable will be designated as x_bardiff and will represent that average difference between After and Before samples.

x_bardiff was calculated by subtracting the Before measurement from the After measurement. This is the intuitive way to determine if a reduction in error occurred.

This problem illustrates why the t-test is nearly always used instead of a z-Test to perform a two-dependent-sample (paired) hypothesis test of mean. The z-Test requires the population standard deviation of the differences between the pairs be known. This is often not the case, but is required for a paired z-Test . The t-test requires only the sample standard deviation of the sample of paired differences be known.

Before and After Results and Their Differences Are As Follows: (Click On Image To See a Larger Version)

Running the Excel data analysis tool Descriptive Statistics on the column of Difference data produces the following output:

Running the Excel data analysis tool Descriptive Statistics on the column of Difference data will provide the Sample Mean, the Sample Standard Deviation, the Standard Error, and the Sample Size. It will even provide half the width of a confidence interval about the mean based on this sample for any specified level of certainty if that option is specified. The output of this tool appears as follows: (Click On Image To See a Larger Version)

It is the difference that we are concerned with. A hypothesis test will be performed on the sample of differences. The distributed variable will be designated as x_bardiff and will represent that average difference between After and Before samples.

x_bardiff was calculated by subtracting the Before measurement from the After measurement. This is the intuitive way to determine if a reduction in error occurred

### Summary of Problem Information

x_bardiff = sample mean =AVERAGE() = -3.35

sdiff = sample standard deviation = STDEV.S() = 6.4

n = sample size = number of pairs = COUNT() = 17

df = n – 1 = 16 SEdiff = Standard Error = sdiff / SQRT(n) = 6.4 / SQRT(16)

SEdiff = 1.55

Note that this calculation of the Standard Error using the sample standard deviation, sdiff, is an estimate of the true Standard Error which would be calculated using the population standard deviation, σdiff.

Level of Certainty = 0.95

Alpha = 1 - Level of Certainty = 1 – 0.95 = 0.05

As with all Hypothesis Tests of Mean, we must satisfactorily answer these two questions and then proceed to the four-step method of solving the hypothesis test that follows.

The Initial Two Questions That Must be Answered Satisfactorily

1) What Type of Test Should Be Done?

2)Have All of the Required Assumptions For This Test Been Met?

The Four-Step Method For Solving All Hypothesis Tests of Mean

Step 1) Create the Null Hypothesis and the Alternate Hypothesis

Step 2 – Map the Normal or t Distribution Curve Based on the Null Hypothesis

Step 3 – Map the Regions of Acceptance and Rejection

Step 4 – Perform the Critical Value Test, the p Value Test, or the Critical t Value Test

The Initial Two Questions That Need To Be Answered Before Performing the Four-Step Hypothesis Test of Mean are as follows:

### Question 1) Type of Test?

a) Hypothesis Test of Mean or Proportion?

This is a Hypothesis Test of Mean because each individual observation (each sampled difference) within the sample can have a wide range of values. Data points for Hypothesis Tests of Proportion are binary: they can take only one of two possible values.

b) One-Sample or Two-Sample Test?

This is a two-sample hypothesis test because the data exists in two groups of measurements. One sample group contains Before measurements and the other sample group contains After measurements.

c) Independent (Unpaired) Test or Dependent (Paired) Test?

This is a paired (dependent) hypothesis test because each Before observation has a related After observation made on the same person.

d) One-Tailed or Two-Tailed Test?

The problem asks to determine whether there has been a reduction in clerical mistake from Before to After. This is a directional inequality making this hypothesis test a one-tailed test. If the problem asked whether Before and After were simply different, the inequality would be non-directional and the resulting hypothesis test would be a two-tailed test. A two-tailed test is more stringent than a one-tailed test.

e) t-Test or z-Test?

Assuming that the difference population or difference sample can pass a normality test, a hypothesis test of mean must be performed as a t-Test when the difference sample size (n = number of difference pairs) is small (n < 30) or if the variance of differences is unknown.

In this case the difference sample size (the number of data pairs) is small as n = 17 data sample pairs. This Hypothesis Test of Mean must therefore be performed as a t-Test and not as a z-Test.

The t Distribution with degrees of freedom = df = n – 1 is defined as the distribution of random data sample of sample size n taken from a normal population.

The means of samples taken from a normal population are also distributed according to the t Distribution with degrees of freedom = df = n – 1.

The Test Statistic (the t Value, which is based upon the difference sample mean (x_bardiff) because it equals (x_bardiff – Constant)/(SEdiff) will therefore also be distributed according to the t Distribution. A t-Test will be performed if the Test Statistic is distributed according to the t Distribution.

The distribution of the Test Statistic for the difference sample taken from a normal population of differences is always described by the t Distribution. The shape of the t Distribution converges to (very closely resembles) the shape of the standard normal distribution when the difference sample size becomes large (n > 30).

The Test Statistic’s distribution can be approximated by the normal distribution only if the difference sample size is large (n > 30) and the population standard deviation, σ, is known. A z-Test can be used if the Test Statistic’s distribution can be approximated by the normal distribution. A t-Test must be used in all other cases.

It should be noted that a paired t-Test can always be used in place of a paired z-Test. All z-Tests can be replaced be their equivalent t-Tests. As a result, some major commercial statistical software packages including the well-known SPSS provide only t-Tests and no direct z-Tests.

This hypothesis test is a t-Test that is two-sample, paired (dependent), one-tailed hypothesis test of mean.

### Question 2) Test Requirements Met?

a) t-Distribution of Test Statistic

A t-Test can be performed if the distribution of the Test Statistic (the t value) can be approximated under the Null Hypothesis by the t Distribution. The Test Statistic is derived from the mean of the difference sample and therefore has the same distribution that the difference sample mean would have if multiple similar samples were taken from the same population of differences between data sample pairs.

The difference sample size indicates how to determine the distribution of the difference sample mean and therefore the distribution of the Test Statistic as follows:

When Difference Sample Size Is Large

When the difference sample size is large (n > 30 meaning that there are more than 30 pairs of data), the distribution of means of similar samples drawn from the same population of differences is described by the t Distribution. As per the Central Limit Theorem, as the difference sample size increases, the distribution of the difference sample means converges to the normal distribution as does the t Distribution. When the difference sample size approaches infinity, the t Distribution converges to the standard normal distribution.

When the difference sample size is large, the distribution of the distribution sample mean, and therefore the distribution of the Test Statistic, is always described by the t Distribution. A t-Test can therefore always be used when the difference sample size is large, regardless of the distribution of the population of differences or the difference sample.

When the Difference Sample Size is Small

The data in a difference sample taken from a normally-distributed population of paired differences will be distributed according to the t Distribution regardless of the difference sample size.

The means of similar difference samples randomly taken from a normally-distributed population of paired differences are also distributed according to the t Distribution regardless of the difference sample size.

The difference sample mean, and therefore the Test Statistic, are distributed according to the t Distribution if the population of paired differences is normally distributed.

The population of paired differences is considered to be normally distributed if any of the following are true:

1) Population of Paired Differences Is Normally Distributed

2) Difference Sample Is Normally Distributed

If the difference sample passes a test of normality then the population of paired difference from which the difference sample was taken can be assumed to be normally distributed.

The population of paired differences or the difference sample must pass a normality test before a t-Test can be performed. If the only data available are the data of the single difference sample taken, then difference sample must pass a normality test before a t-Test can be performed.

### Evaluating the Normality of the Sample Data

In the next blog article the following five normality tests will be performed on the difference sample data here:

An Excel histogram of the sample data will be created.

A normal probability plot of the sample data will be created in Excel.

The Kolmogorov-Smirnov test for normality of the sample data will be performed in Excel.

The Anderson-Darling test for normality of the sample data will be performed in Excel.

The Shapiro-Wilk test for normality of the sample data will be performed in Excel.

We now proceed to complete the four-step method for solving all Hypothesis Tests of Mean. These four steps are as follows:

Step 1) Create the Null Hypothesis and the Alternate Hypothesis

Step 2 – Map the Normal or t Distribution Curve Based on the Null Hypothesis

Step 3 – Map the Regions of Acceptance and Rejection

Step 4 – Determine Whether to Accept or Reject theNull Hypothesis By Performing the Critical Value Test, the p Value Test, or the Critical t Value Test

Proceeding through the four steps is done is follows:

### Step 1 – Create the Null and Alternate Hypotheses

The Null Hypothesis is always an equality and states that the items being compared are the same. In this case, the Null Hypothesis would state that the there is no difference between before and after data. We will use the variable x_bardiff to represent the mean between the before and after measurements. The Null Hypothesis is as follows:

H0: x_bardiff = Constant = 0

The Alternate Hypothesis is always in inequality and states that the two items being compared are different. This hypothesis test is trying to determine whether there has been a reduction in clerical errors, i.e., the After measurements are, on average, smaller than the Before measurements. The Alternate Hypothesis is as follows:

H1: x_bardiff < Constant , which is 0

H1: x_bardiff < 0

The Alternative Hypothesis is directional (“greater than” or “less than” instead of “not equal,” which is non-directional) and the hypothesis test is therefore a one-tailed test. The “less than” operator indicates that this is a one-tailed test with the Region of Rejection (the alpha region) entirely contained in the left tail. A “greater than” operator would indicate a one-tailed test focused on the right tail.

It should also be noted that a two-tailed test is more rigorous (requires a greater differences between the two entities being compared before the test shows that there is a difference) than a one-tailed test.

It is important to note that the Null and Alternative Hypotheses refer to the means of the population of paired differences from which the difference samples were taken. A population of paired differences would be the differences of data pairs in a population of data pairs.

A paired t-Test determines whether to reject or fail to reject the Null Hypothesis that states that that population of paired differences from which the difference sample was taken has a mean equal to the Constant. The Constant in this case is equal to 0. This means that the Null Hypothesis states that the average difference between data pairs of an entire population from which the sample of data pairs were drawn is zero.

Parameters necessary to map the distributed variable, x_bardiff, are the following:

x_bardiff = sample mean =AVERAGE() = -3.35

sdiff = sample standard deviation = STDEV.S() = 6.4

n = sample size = number of pairs = COUNT() = 17

df = n – 1 = 16 SEdiff = Standard Error = sdiff / SQRT(n) = 6.4 / SQRT(16)

These parameters are used to map the distributed variable, x_bardiff, to the t Distribution curve as follows:

### Step 2 – Map the Distributed Variable to t-Distribution

A t-Test can be performed if the difference sample mean, and the Test Statistic (the t Value) are distributed according to the t Distribution. If the difference sample has passed a normality test, then the difference sample mean and closely-related Test Statistic are distributed according to the t Distribution.

The t Distribution always has a mean of zero and a standard error equal to one. The t Distribution varies only in its shape. The shape of a specific t Distribution curve is determined by only one parameter: its degrees of freedom, which equals n – 1 if n = sample size.

The means of similar, random difference samples taken from a normal population of paired differences are distributed according to the t Distribution. This means that the distribution of a large number of means of difference samples of size n taken from a normal population will have the same shape as a t Distribution with its degrees of equal to n – 1.

The difference sample mean and the Test Statistic are both distributed according to the t Distribution with degrees of freedom equal to n – 1 if the sample or population is shown to be normally distributed. This step will map the sample mean to a t Distribution curve with a degrees of freedom equal to n – 1.

The t Distribution is usually presented in its finalized form with standardized values of a mean that equals zero and a standard error that equals one. The horizontal axis is given in units of Standard Errors and the distributed variable is the t Value (the Test Statistic) as follows in this Excel-generated graph: (Click On Image To See a Larger Version)

A non-standardized t Distribution curve would simply have its horizontal axis given in units of the measure used to take the samples. The distributed variable would be the sample mean, x_bardiff.

The variable x_bardiff is distributed according to the t Distribution. Mapping this distributed variable to a t Distribution curve is shown as follows in this Excel-generated graph: (Click On Image To See a Larger Version)

This non-standardized t Distribution curve has its mean set to equal the Constant taken from the Null Hypothesis, which is:

H0: x_bardiff = Constant = 0

This non-standardized t Distribution curve is constructed from the following parameters:

Mean = Constant = 0

Standard Errordiff = 1.55

Degrees of Freedom = 16

Distributed Variable = x_bardiff

### Step 3 – Map the Regions of Acceptance and Rejection

The goal of a hypothesis test is to determine whether to accept or reject the Null Hypothesis at a given level of certainty. If the two things being compared are far enough apart from each other, the Null Hypothesis (which states that the two things are not different) can be rejected. In this case we are trying to show graphically how different x_bardiff (-3.35) is from the hypothesized mean of 0.

The non-standardized t Distribution curve can be divided up into two types of regions: the Region of Acceptance and the Region of Rejection. A boundary between a Region of Acceptance and a Region of Rejection is called a Critical Value.

The above distribution curve that maps the distribution of variable x_bardiff can be divided up into two types of regions: the Region of Acceptance and the Region of Rejection.

If x_bardiff’s value of -3.35 falls in the Region of Acceptance, we must accept the Null Hypothesis. If x_bardiff’s value of -3.35 falls in the Region of Rejection, we can reject the Null Hypothesis.

The total size of the Region of Rejection is equal to Alpha. In this case Alpha, α, is equal to 0.05. This means that the Region of Rejection will take up 5 percent of the total area under this t distribution curve.

This 5 percent is entirely contained in the outer left tail. The outer left tail contains the 5 percent of the curve that is the Region of Rejection.

Calculate the Critical Value

The boundary between Region of Acceptance and Region of Rejection is called Critical Value. The location of this Critical Value need to be calculated as follows.

One-tailed, Left tail Critical Value = x_bardiff - (Number of Standard Errors from Mean to Region of Rejection) * SEdiff

One-tailed, Left tail Critical Value = x_bardiff + T.INV(α,df) * SEdiff

One-tailed, Left tail Critical Value = 0 + T.INV(0.05, 16) * 1.55

One-tailed, Left tail Critical Value = -2.711

The Region of Rejection is therefore everything that is to the left of -2.711.

The distribution curve with the blue 95-percent Region of Acceptance and the yellow 5-percent Region of Rejection entirely contained in the left tail is shown is as follows in this Excel-generated graph: (Click On Image To See a Larger Version)

### Step 4 – Determine Whether to Reject Null Hypothesis

The object of a hypothesis test is to determine whether to accept of reject the Null Hypothesis. There are three equivalent-Tests that determine whether to accept or reject the Null Hypothesis. Only one of these tests needs to be performed because all three provide equivalent information. The three tests are as follows:

1) Compare x-bardiff With Critical Value

Reject the Null Hypothesis if the sample mean, x_bardiff = -3.35, falls into the Region of Rejection.

Equivalently, reject the Null Hypothesis if the sample mean, x_bardiff, is further the curve’s mean of 0 than the Critical Value.

The Critical Values have been calculated to be -2.71 on the left. x_bardiff (-3.35) is further from the curve mean (0) than left Critical Value (-2.71). The Null Hypothesis would therefore be rejected.

2) Compare t Value With Critical t Value

The t Value corresponds to the standardized value of the sample mean, x_bardiff = -3.35. The t Value is the number of Standard Errors that x_bardiff is from the curve’s mean of 0.

The Critical t Value is the number of Standard Errors that the Critical Value is from the curve’s mean.

Reject the Null Hypothesis if the t Value is farther from the standardized mean of zero than the Critical t Value.

Equivalently, reject the Null Hypothesis if the t Value is closer to the standardized mean of zero than the Critical t Value. t Value (Test Statistic) = (x_bardiff) / SEdiff = (-3.35)/1.55

t Value (Test Statistic) = -2.159

This means that the sample mean, x_bardiff, is 2.159 standard errors to the left of the curve mean of 0.

One-tailed, left-tail Critical t Value = T.INV(α,df)

One-tailed, left-tail Critical t Value = T.INV(0.05, 16) = -1.76

This means that the boundary of the Region of Rejection are 1.76 standard errors to the left of the curve mean of 0 since this is a one-tailed test in the left tail.

The Null Hypothesis is rejected because the t Value is farther from curve mean the Critical t Values indicating that x_bardiff is in the Region of Rejection.

3) Compare p Value With Alpha

The p Value is the percent of the curve that is beyond x_bardiff (-3.35). If the p Value is smaller than Alpha, the Null Hypothesis is rejected.

p Value = T.DIST.RT(ABS(t Value), df)

p Value = T.DIST.RT(ABS(-2.159), 16)

p Value = 0.023

The p Value (0.023) is smaller than Alpha (0.05) Region of Rejection in the right tail and we therefore reject the Null Hypothesis. A graph below shows that the red p Value (the curve area beyond x_bar) is smaller than the yellow Alpha, which is the 5 percent Region of Rejection in the left tail. This is shown in the following Excel-generated graph of this non-standardized t Distribution curve: (Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP