## Tuesday, May 27, 2014

### Paired t-Test Excel Calculations

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

# Excel Calculations, Formulas,and Tools For the Paired t-Test

## Excel Data Analysis Tool Shortcut

This two-independent-sample, Pooled t-Test can be solved much quicker using the following Excel data analysis tool:

t-Test: Paired Two Sample for Means

The Excel tool can be found by clicking Data Analysis under the Data tab. The tool is titled t-Test:Paired Two Sample For Means. The entire Data Analysis Toolpak is an add-in that ships with Excel but must first be activated by the user before it is available.

This hypothesis test creates the sample of differences by subtracting the Before results from the After results. If the training program has successfully reduced the average number monthly clerical errors per employee, the resulting average difference (x_bardiff) will be negative.

If the Before data was subtracted from the After data, the After data (in column B) sample should be designated as Variable 1, as is done here. This ensures that the t Value (T Stat in the Excel output) has the correct sign, which would be negative in this case.

This tool will be applied to the following data set using the same data as the preceding example in this section.

The completed dialogue box for this tool is shown as follows:

Clicking OK will produce the following result. This result agrees with the calculations that were performed in this section.

The calculations to create the preceding output were performed as follows. The individual outputs are color-coded so it is straight-forward to match the calculations with the outputs of the tool.

## Excel Statistical Function Shortcut

The stand-alone Excel formula to perform a paired (two-dependent sample) t-Test is shown as follows. If the resulting p Value is smaller than α for a one-tailed test or α/2 for a two-tailed test, the mean difference between the Before and After sample pairs is deemed to be statistically significant.

The p Value is calculated to be 0.023. This is less than Alpha (0.05) or Alpha/2 (0.025) so the Null Hypothesis for this t-Test would be rejected for both a one-tailed test and a two-tailed test if Alpha is set to 0.05 (95 percent certainty required for the test).

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
• 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