How do you run at test in Excel 2022?

Microsoft Excel for Mac computers contains many of the same functions as Excel for Windows , including the t-test function. To perform a t-test, you need two sets of data to compare; the data should be placed in two columns on an Excel worksheet. You can then perform the t-test in any blank cell on the worksheet.

Set Up the Worksheet

There are three types of t-tests available using this Excel function, so you should also add a description beside the result to identify which test was used and whether you used one tail or two tails in the calculation.

Separate your two data-sets into labeled columns, and label the third column to represent the t-test results. The formula will calculate across the first two rows while displaying the calculation in the third row. This means you must have the correct data to calculate against in each row. Otherwise, the entire setup process is very straightforward. The steps are the same on Mac and PC systems, because Excel is essentially the same on both platforms.

Run the T-Test

Type “Data A” in cell A1 and “Data B” in cell B1. Type the set of data you have collected from the first sample in the first column, and the second set in the second column. If you don’t have data on hand, type random numbers in cells A2 to A10, then in cells B2 to B10.

Begin by clicking on any blank cell to do the t-test. Type “=TTEST(“ to begin the function. The syntax for this function displays below the cell: “TTEST(Array1, Array2, tails, type)."

Create the First Array by dragging the cursor over the first column of data beginning with cell A2. This is “Array 1.” Type a comma. The text in the cell now contains “=ttest(A2:A10."

Create the Second Array by dragging the cursor over the second column of data to add the second array. Type a comma. You now see “=ttest(A2:A10, B2:B10,” in the cell.

Now you must enter the "Tail" value. Type “1” or “2” for the “tail” value, then type another comma. Note that t-test “tail” refers to the tails on a line graph if the data were charted in a graph. You can use the value “1” if you are only interested in the data in one tail of the graph or “2” if both tails are important.

Enter the "Type" value next. Type “1,” “2,” or “3” for the “type” value, then type a “)” to complete the function. Note that the t-test “type” refers to the type of t-test you want to perform. Use the value “1” to perform a paired test. Use the value “2” to use a homoscedastic t-test with a two-sample equal variance. Type the value “3” to use a heteroscedastic t-test with a two-sample unequal variance.

Press Enter to execute the Excel test function and return the desired values. The function is now replaced with a value representing the result of the t-test.

ToolPak Shortcut Options

Excel also offers a tool kit called the ToolPak. This provides shortcuts for executing statistical calculations without requiring the extensive steps to complete the formulas. If you use Excel for engineering or any statistical purposes regularly, the ToolPak is a huge time-saver and an easy install through the Excel add-ins located under the Options tab.

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.

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

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.

Now imagine you roll each die 6 times:

• The first die rolls 3, 6, 6, 4, 3, 3; Mean = 4.17
• The 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.

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

Checking and Loading the Analysis Toolpak Add-In

To check and activate the Analysis Toolpak follow these steps:

1. Select the FILE tab >select Options.

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

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

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

5. 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

1. 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.

2. Select the Data tab > Data Analysis

3. 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.

4. 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.

5. 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.

6. 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.

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

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

9. 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.

10. 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.

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Subscribe

How do I use the t

How do I do at test in Excel 365?

How do I do a one sample t

How do I create a test data in Excel?