How To Do A T Test In Excel

A T-test is a way of deciding if there are statistically significant differences between datasets, using a Student’s t-distribution. The T-Test in Excel is a two-sample T-test comparing the means of two samples. This article explains what statistical significance means and shows how to do a T-Test in Excel.

What is Statistical Significance?

Imagine you want to know which of two dice will give a better score. You roll the first die and get a 2; you roll the second die and get a 6. Does this tell you the second die usually gives higher scores? If you answered, “Of course not,” then you already have some understanding of statistical significance. You understand the difference was due to the random change in the score, each time a die is rolled. Because the sample was very small (only one roll) it didn’t show anything significant.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Microsoft 365 and Excel Online.

Now imagine you roll each die 6 times:

• The first die rolls 3, 6, 6, 4, 3, 3; Mean = 4.17The second die rolls 5, 6, 2, 5, 2, 4; Mean = 4.00

Does this now prove the first die gives higher scores than the second? Probably not. A small sample with a relatively small difference between the means makes it likely the difference is still due to random variations. As we increase the number of dice rolls it becomes difficult to give a common sense answer to the question — is the difference between the scores the result of random variation or is one actually more likely to give higher scores than the other?

Significance is the probability that an observed difference between samples is due to random variations. Significance is often called the alpha level or simply ‘α.’ The confidence level, or simply ‘c,’ is the probability that the difference between the samples is not due to random variation; in other words, that there’s a difference between the underlying populations. Therefore: c = 1 – α

We can set ‘α’ at whatever level we want, to feel confident we’ve proven significance. Very often α=5% is used (95% confidence), but if we want to be really sure that any differences are not caused by random variation, we might apply a higher confidence level, using α=1% or even α=0.1%.

Various statistical tests are used to calculate significance in different situations. T-tests are used to determine whether the means of two populations are different and F-tests are used to determine whether the variances are different.

Why Test for Statistical Significance?

When comparing different things, we need to use significance testing to determine if one is better than the other. This applies to many fields, for example:

• In business, people need to compare different products and marketing methods.In sports, people need to compare different equipment, techniques, and competitors.In engineering, people need to compare different designs and parameter settings.

If you want to test whether something performs better than something else, in any field, you need to test for statistical significance.

What is a Student’s T-Distribution?

A Student’s t-distribution is similar to a normal (or Gaussian) distribution. These are both bell-shaped distributions with most results close to the mean, but some rare events are quite far from the mean in both directions, referred to as the tails of the distribution.

The exact shape of the Student’s t-distribution depends on the sample size. For samples of more than 30 it’s very similar to the normal distribution. As the sample size is reduced, the tails get larger, representing the increased uncertainty that comes from making inferences based on a small sample.

How to Do a T-Test in Excel

Before you can apply a T-Test to determine whether there’s a statistically significant difference between the means of two samples, you must first perform an F-Test. This is because different calculations are performed for the T-Test depending on whether there’s a significant difference between the variances.

Checking and Loading the Analysis Toolpak Add-In

To check and activate the Analysis Toolpak follow these steps:

You will need the Analysis Toolpak add-in enabled to perform this analysis.

• Select the FILE tab >select Options.
• In the Options dialogue box, select Add-Ins from the tabs on the left-hand side.
• At the bottom of the window, select the Manage drop-down menu, then select Excel Add-ins. Select Go.
• Ensure the check-box next to Analysis Toolpak is checked, then select OK.
• The Analysis Toolpak is now active and you are ready to apply F-Tests and T-Tests.

Performing an F-Test and a T-Test in Excel

• Enter two datasets into a spreadsheet. In this case, we’re considering the sales of two products during a week. The mean daily sales value for each product is also calculated, together with its standard deviation.
• Select the Data tab > Data Analysis
• Select F-Test Two-Sample for Variances from the list, then select OK.
• The F-Test is highly sensitive to non-normality. It may therefore be safer to use a Welch test, but this is more difficult in Excel.
• Select the Variable 1 Range and Variable 2 Range; set the Alpha (0.05 gives 95% confidence); select a cell for the top left corner of the output, considering that this will fill 3 columns and 10 rows. Select OK.
• For the for Variable 1 Range, the sample with the largest standard deviation (or variance) must be selected.
• View the F-Test results to determine whether there is a significant difference between the variances. The results give three important values:
• F: The ratio between the variances.P(F<=f) one-tail: The probability that variable 1 doesn’t actually have a larger variance than variable 2. If this is larger than alpha, which is generally 0.05, then there’s no significant difference between the variances.F Critical one-tail: The value of F that would be required to give P(F<=f)=α. If this value is greater than F, this also indicates there’s no significant difference between the variances.
• P(F<=f) can also be calculated using the FDIST function with F and the degrees of freedom for each sample as its inputs. Degrees of freedom is simply the number of observations in a sample minus one.
• Now that you know whether there is a difference between the variances you can select the appropriate T-Test. Select the Data tab > Data Analysis, then select either t-Test: Two-Sample Assuming Equal Variances or t-Test: Two-Sample Assuming Unequal Variances.
• Regardless of which option you chose in the previous step, you will be presented with the same dialogue box to enter the details of the analysis. To start, select the ranges containing the samples for Variable 1 Range and Variable 2 Range.
• Assuming you want to test for no difference between the means, set the Hypothesized Mean Difference to zero.
• Set the significance level Alpha (0.05 gives 95% confidence), and select a cell for the top left corner of the output, considering that this will fill 3 columns and 14 rows. Select OK.
• Review the results to decide if there’s a significant difference between the means.
• Just as with the F-Test, if the p-value, in this case P(T<=t), is greater than alpha, then there’s no significant difference. However, in this case there are two p-values given, one for a one-tail test and the other for a two-tail test. In this case, use the two-tail value since either variable having a greater mean would be a significant difference.

Select the FILE tab >select Options.

In the Options dialogue box, select Add-Ins from the tabs on the left-hand side.

At the bottom of the window, select the Manage drop-down menu, then select Excel Add-ins. Select Go.

Ensure the check-box next to Analysis Toolpak is checked, then select OK.

The Analysis Toolpak is now active and you are ready to apply F-Tests and T-Tests.

Enter two datasets into a spreadsheet. In this case, we’re considering the sales of two products during a week. The mean daily sales value for each product is also calculated, together with its standard deviation.

Select the Data tab > Data Analysis

Select F-Test Two-Sample for Variances from the list, then select OK.

The F-Test is highly sensitive to non-normality. It may therefore be safer to use a Welch test, but this is more difficult in Excel.

Select the Variable 1 Range and Variable 2 Range; set the Alpha (0.05 gives 95% confidence); select a cell for the top left corner of the output, considering that this will fill 3 columns and 10 rows. Select OK.

For the for Variable 1 Range, the sample with the largest standard deviation (or variance) must be selected.

View the F-Test results to determine whether there is a significant difference between the variances. The results give three important values:

• F: The ratio between the variances.P(F<=f) one-tail: The probability that variable 1 doesn’t actually have a larger variance than variable 2. If this is larger than alpha, which is generally 0.05, then there’s no significant difference between the variances.F Critical one-tail: The value of F that would be required to give P(F<=f)=α. If this value is greater than F, this also indicates there’s no significant difference between the variances.

P(F<=f) can also be calculated using the FDIST function with F and the degrees of freedom for each sample as its inputs. Degrees of freedom is simply the number of observations in a sample minus one.

Now that you know whether there is a difference between the variances you can select the appropriate T-Test. Select the Data tab > Data Analysis, then select either t-Test: Two-Sample Assuming Equal Variances or t-Test: Two-Sample Assuming Unequal Variances.

Regardless of which option you chose in the previous step, you will be presented with the same dialogue box to enter the details of the analysis. To start, select the ranges containing the samples for Variable 1 Range and Variable 2 Range.

Assuming you want to test for no difference between the means, set the Hypothesized Mean Difference to zero.

Set the significance level Alpha (0.05 gives 95% confidence), and select a cell for the top left corner of the output, considering that this will fill 3 columns and 14 rows. Select OK.

Review the results to decide if there’s a significant difference between the means.

Just as with the F-Test, if the p-value, in this case P(T<=t), is greater than alpha, then there’s no significant difference. However, in this case there are two p-values given, one for a one-tail test and the other for a two-tail test. In this case, use the two-tail value since either variable having a greater mean would be a significant difference.

Get the Latest Tech News Delivered Every Day