• Skip to secondary menu
  • Skip to main content
  • Skip to primary sidebar

Statistics By Jim

Making statistics intuitive

How to do t-Tests in Excel

By Jim Frost 120 Comments

Excel can perform various statistical analyses , including t-tests. It is an excellent option because nearly everyone can access Excel. This post is a great introduction to performing and interpreting t-tests even if Excel isn’t your primary statistical software package.

In this post, I provide step-by-step instructions for using Excel to perform t-tests. Importantly, I also show you how to select the correct form of t-test, choose the right options, and interpret the results. I also include links to additional resources I’ve written, which present clear explanations of relevant t-test concepts that you won’t find in Excel’s documentation. And, I use an example dataset for us to work through and interpret together!

Excel logo

  • Two group means are different.
  • Paired means are different.
  • One mean is different from a target value.

For more information about the types of t-tests you can use, read my T-Test Overview post. Also, learn how it compares to the Z Test .

Install the Data Analysis ToolPak in Excel

The Data Analysis ToolPak must be installed on your copy of Excel to perform t-tests. To determine whether you have this ToolPak installed, click Data in Excel’s menu across the top and look for Data Analysis in the Analyze section. If you don’t see Data Analysis, you need to install it. Don’t worry. It’s free!

Excel menu with Data Analysis ToolPak.

Note : Excel cannot perform a one-sample t-test .

Two-Sample t-Tests in Excel

Two-sample t-tests compare the means of precisely two groups—no more and no less! Typically, you perform this test to determine whether two population means are different. For example, do students who learn using Method A have a different mean score than those who learn using Method B? This form of the test uses independent samples. In other words, each group contains a unique set of people or items.

Statisticians consider differences between group means to be an unstandardized effect size because these values indicate the strength of the relationship using values that retain the natural units of the dependent variable. Cohen’s d is the corresponding standardized effect size and it’s appropriate to report in some cases. Effect sizes help you understand how important the findings are in a practical sense. To learn more about unstandardized and standardized effect sizes, read my post about Effect Sizes in Statistics .

The standard form tests the following hypotheses:

  • Null : The two population means are equal.
  • Alternative : The two population means are not equal.

If the p-value is less than your significance level (e.g., 0.05), you can reject the null hypothesis. The difference between the two means is statistically significant. Your sample provides strong enough evidence to conclude that the two population means are different.

For more information about the null and alternative hypotheses and other hypothesis testing terms, see my Hypothesis Testing Overview .

Also, learn about the difference between descriptive statistics and inferential statistics .

t-Tests for Equal and Unequal Variances

You’ll notice that Excel has two forms of the two-sample t-test. One that assumes equal variances and the other that assumes unequal variances. Variances and the closely related standard deviation are measures of variability . All t-tests assume you obtained data from normally distributed populations. However, the conventional t-test also assumes the standard deviations/variances for both groups are equal. Another form of the test, known as Welch’s t-test, does not assume equal variances.

As an aside, thanks to the central limit theorem , you can safely use t-tests to analyze nonnormal data when have ~20 or more observations per group.

Which One to Use?

Advice for using either the equal or unequal variances form of the 2-sample t-test varies because this issue is more complicated than it first appears. Some analysts advise using an F-test to determine whether the variances are unequal. And, Excel does offer the F-test Two-Sample for Variances. However, using additional tests always increases the probability of both false positives and false negatives (a.k.a, Type I and Type II errors ).

Additionally, if you have a large sample size, the f-test has more statistical power . This condition can cause the test to identify an inconsequential difference as being statistically significant. That’s the difference between practical significance and statistical significance . Conversely, small sample sizes can fail to detect a substantial difference between variances.

When you have an equal, or nearly equal, number of observations in both groups and a moderate sample size, t-tests are robust to differences between variances. If you find one group has twice the variance of another group, it might be time to worry! However, you don’t need to worry about smaller differences.

Other analysts suggest always using the form of the t-test that assumes unequal variances. If you use this approach when the variances are equal, you lose a trivial amount of statistical power, but you’ll be better off when the variances are not equal.

If you have unequal variances and unequal samples sizes, it’s vital to use the unequal variances version of the 2-sample t-test!

Step-by-Step Instructions for Running the Two-Sample t-Test in Excel

Let’s conduct a two-sample t-test! This test is also known as the independent samples t-test . Click the link to learn more about its hypotheses, assumptions, and interpretation.

Our hypothetical scenario is that we are comparing scores from two teaching methods. We drew two random samples of students. One sample comprises students who learned using Method A while the other sample learned using Method B. These samples contain entirely different students. Now, we want to determine whether the two means are different. Download the CSV file that contains all data for both t-test examples in this post: t-TestExamples .

To perform a 2-sample t-test in Excel, arrange your data in two columns, as shown below.

Dataset for 2-sample t-test analysis using Excel.

  • In Excel, click Data Analysis on the Data tab.
  • From the Data Analysis popup, choose t-Test: Two-Sample Assuming Equal Variances .
  • Under Input , select the ranges for both Variable 1 and Variable 2 .
  • In Hypothesized Mean Difference , you’ll typically enter zero. This value is the null hypothesis value, which represents no effect. In this case, a mean difference of zero represents no difference between the two methods, which is no effect.
  • Check the Labels checkbox if you have meaningful variable names in row 1. This option makes the output easier to interpret. Ensure that you include the label row in step #3.
  • Excel uses a default Alpha value of 0.05, which is usually a good value. Alpha is the significance level. Change this value only when you have a specific reason for doing so.

For the example data, your popup should look like the image below:

Two-sample t-test setup in Excel.

Interpreting the Two-Sample t-Test Results

Excel's 2-sample t-test statistical output.

If the p-value is less than your significance level, the difference between means is statistically significant. Excel provides p-values for both one-tailed and two-tailed t-tests.

One-tailed t-tests can detect differences between means in only one direction. For example, a one-tailed test might determine only whether Method B is greater than Method A. Two-tailed tests can detect differences in either direction—greater than or less than. There are additional drawbacks for using one-tailed tests—so I’ll stick with the standard two-tailed results. To learn more, read my post about one-tailed and two-tailed tests .

For our results, we’ll use P(T<=t) two-tail, which is the p-value for the two-tailed form of the t-test. Because our p-value (0.000336) is less than the standard significance level of 0.05, we can reject the null hypothesis. Our sample data support the hypothesis that the population means are different. Specifically, Method B’s mean is greater than Method A’s mean.

Paired t-Tests in Excel

Paired t-tests assess paired observations, which are often two measurements on the same person or item. Statisticians call these dependent samples. Suppose you gather a random sample of people. You give them all a pretest, administer a treatment, and then perform a posttest. Each subject has a pretest and posttest score. Or, perhaps you have a sample of wood boards, and you paint half of each board with one paint and the other half with different paint. Then, you measure the paint durability for both types of paint on all the boards. Each board has two paint durability scores.

In both cases, you can use a paired t-test to determine whether the difference between the means of the two sets of scores is statistically significant.

Unlike independent t-tests, paired t-tests use the same people or items in both groups. One way to determine whether a paired t-test is appropriate for your data is if each row in the dataset corresponds to one person or item. For our pretest/posttest example, we measure each subject before and after the experiment and placed the measurements for an individual on one row.

Related posts : Independent and Dependent Samples and Paired T Test

Step-by-Step Instructions for Running the Paired t-Test in Excel

For this example, imagine that we have a training program, and we need to determine whether the difference between the mean pretest score and the mean posttest score is significantly different.

To perform a paired t-test in Excel, arrange your data into two columns so that each row represents one person or item, as shown below. Note that the analysis does not use the subject’s ID number.

Dataset for paired t-test using Excel.

  • From the Data Analysis popup, choose t-Test: Paired Two Sample for Means .
  • Check the Labels checkbox if you have meaningful variables labels in row 1. This option helps make the output easier to interpret. Ensure that you include the label row in step #3.

Excel's popup for setting up the paired t-test.

Interpreting Excel’s Paired t-Test Results

Excel's paired t-test statistical output.

If the p-value is less than your significance level, the difference between means is statistically significant. Again, Excel provides p-values for both one-tailed and two-tailed t-tests—and we’ll stick with the two-tailed result. For information about the other statistics, click the links in the 2-sample t-test section.

For our results, we’ll use P(T<=t) two-tail, which is the p-value for the two-tailed form of the t-test. Because our p-value (0.002221) is less than the standard significance level of 0.05, we can reject the null hypothesis. Our sample data support the hypothesis that the population means are different. Specifically, the Posttest mean is greater than the Pretest mean.

What Excel’s t-Tests Do Not Include

As nice as it is to be able to perform t-tests in Excel, it leaves out some essential features. Notably, Excel cannot create confidence intervals. The means in these analyses are the point estimates for the population means. However, thanks to random error, the sample means never precisely equal the population mean. There is a margin of error around the estimates. Confidence intervals use a margin of error to calculate a range of values that is likely to contain the actual population mean for each group. Learn more about confidence intervals .

Excel also doesn’t calculate the estimated difference between the means. The difference between the means is the effect size for the analysis—an important value to know. By using a formula in Excel, you can easily calculate the difference between means. However, it would be nice to have a confidence interval for this difference too. For more information, read my post about using confidence intervals to assess differences between means .

Finally, Excel, strangely, does not provide a one-sample t-test! In some cases, you might have a single sample of data and need to determine whether it is different from a target value. For example, you might measure the strength of a product and use a one-sample t-test to determine whether it is significantly different from an important strength value.

t-Tests can compare up to two groups. If you have three or more groups, you’ll need to use ANOVA. For more information, see my posts about how to do one-way ANOVA in Excel  and how to do two-way ANOVA in Excel ! Learn about the T Distribution .

If you want to learn more about how t-tests work, read my post 1-sample, 2-sample, and Paired t-Tests .

If you’re learning about hypothesis testing and like the approach I use in my blog, check out my Hypothesis Testing book! You can find it at Amazon and other retailers.

Cover image of my Hypothesis Testing: An Intuitive Guide ebook.

Share this:

hypothesis testing excel

Reader Interactions

' src=

February 27, 2024 at 11:03 pm

Hey Jim, Thanks fir this post. Had question regarding paired t-test. Due to an intervention, I am using paired t-test to understand the impact on sales. Lets say we already know for that our business has gone down. So the difference in mean sales would be negative. How to factor in the %age of declining business factor, so that negative difference of mean is not an indicative of intervention. Thank you

' src=

February 27, 2024 at 11:46 pm

I think I’m understanding your question, but if I’m not let me know.

You have an intervention but you also know that there’s a negative sales trend but presumably your intervention was to increase sales. So, you want to see if the effect was significantly positive even if sales declined. Basically, sales might have gone down but less than expected due to the overall decline thanks to your intervention?

One approach would be to perform a paired t-test as you mention but change the null hypothesis to account for the downturn. Suppose business declines by 10% and the average pre-test sale is 50. Therefore, you’d expect a drop of -5 from the pre-test to post-test. Usually, a paired t-test’s null hypothesis evaluates whether the mean difference = 0. In your case, you change the null so it tests whether the mean difference = -5. (Of course, use your own value.) So, if the actual mean difference from the data was -1, the paired t-test would see if that is greater than -5. It might be significant even though it is negative. A significant result implies that the post-test values are significantly higher than what would be expected under the null hypothesis of a -5 decline.

The success of this approach heavily depends on how accurately the expected decline (-5 in my example) is estimated. Miscalculation or misestimation could lead to incorrect conclusions. Most statistical software will allow you to change the value in the null hypothesis.

A more sophisticated approach would be to consider using a regression model that includes control variables that can account for other factors influencing sales, like market trends, seasonal effects, or economic conditions. This method allows for the quantification of the specific impact of the intervention while controlling for other factors. It’s a powerful approach for isolating the effect of the intervention from general trends and seasonal effects.

Another possibility is to perform an interrupted time series analysis (ITSA). If you have sales data collected at multiple points in time before and after the intervention, ITSA can be a powerful method. It allows you to model the sales trend over time and assess how the intervention has impacted this trend. This is particularly useful if there’s an ongoing decline, as it helps in understanding whether the intervention slowed down, halted, or reversed the decline.

The latter two approaches are more complex and require more data. It depends if the simple paired t-test with adjusted null hypothesis is good enough for your needs or not and how much time and resources you have for doing a more complex analysis.

February 28, 2024 at 12:08 am

Thanks Jim for detail feedback 🙂 Super insightful

February 28, 2024 at 12:13 am

You’re very welcome!

' src=

February 25, 2024 at 9:57 pm

Thanks you so much for this. Very clear. Can you tell me if you have found a way to have a malleable input? I have changing data and would like my dashboard to update without having ro rerun the T tests. Is there a way to do this that you know of?

February 25, 2024 at 10:42 pm

I’m not sure what you mean by “malleable input?”

It sounds like you might want to continually perform t-tests as new data comes in? If so, that’s not recommended because it tends to generate false positives. Suppose you have a case where the null hypothesis is true. Then you continually perform t-tests as data comes in. Most results are negative. But you might get a positive result at some point purely due to chance. You’ll stop testing and state you have significant results even though all you did was test through a bunch of negative results until you got one positive result by chance.

Instead, define how many samples you’ll collect. Collect the data. Then perform a single t-test.

' src=

July 3, 2022 at 7:31 pm

Hi Jim, I wasn’t sure how to start a new comment but found I can post a reply. I am doing comparisons of gene expression levels between normal and diseased samples. There are 100 genes to do individual t-tests, sample sizes are 15 normal and 12 diseased. I initially used two sample assuming equal variances after doing random F-tests. However, I realised that there might be a few with unequal variances. Should I do an F-test for each gene? Is it possible to do both equal and unequal variances depending on the f-test result or should I just simply assume unequal variances for all? Also, is the Benjamini-Hochberg method the best correction to use? Thank you in advance.

' src=

March 1, 2022 at 7:18 am

very clear explaination Please guide me how to rpeort these in APA stye

' src=

January 28, 2022 at 1:56 am

Thanks for your thorough reply. In that case, seems that there is no advantage for doing two tail test then? My understanding is the point for doing both side is you want to determine if H1 can be better or worse than H0. If excel did a one tail test in two ways at the same time, it seems that it can determine if H1 is better or worse at the same time.

This also leads to my 2nd question, why performing a both one-sided study at the same time is a NO-NO?

January 28, 2022 at 5:37 pm

If you perform both one-sided tests with a significance level of 0.05, that’s equivalent to a two-sided test with a significance level of 0.10. That test requires weaker evidence to be significant, which allows more false positives.

In some cases, that might be ok if the problems associated with a false positive aren’t too bad. However, when that’s the case, just perform the two-sided test with a significance level of 0.10!

I’ve written about when to perform one-tailed tests . That should answer your questions. Usually, when you have a choice to make about one- or two-tails, you’ll want to go with two-tails. However, there are exceptions. I go over that in that post!

January 27, 2022 at 4:49 am

Hi Jim, Quick question about one-tail. Can we choose the driection of the tail in excel? i.e. left or right tail? I wonder if excel has a default direction of the tail. Thanks, Rex

January 27, 2022 at 10:14 pm

Excel is a bit weird about this issue. You can’t specify one- or two-tailed tests. Nor can you specify the direction for one-tailed tests. But it ends up not being a problem, well, not completely at least!

Excel takes the first column and subtracts the second column from it to calculate the t-value. In the two-sample example, my t Stat is negative because the second column is larger than the first. However, Excel lists a positive critical value for this test. Hence, it should be impossible to detect a significant effect in a one-sided test as it looks like its testing the wrong direction. However, the one-sided results are, in fact, statistically significant. Excel must be taking the absolute value of the t Stat and comparing it to the critical value. In effect, this allows Excel to perform both one-sided tests at the same time, which is a NO-NO!

When you perform a one-tailed test, you should pick the direction before performing the test and then only perform the test in that one direction–not perform both like Excel.

At any rate, when using Excel, decide the direction first. Then, if you have significant results, just be sure it was in the direction you specified.

While you can’t specify the direction, it’s not really a problem other than that issue of performing both one-sided tests, which you can watch out for!

' src=

October 20, 2021 at 5:23 am

Hi Jim, thanks for the useful article. I would like to clarify on some matters.

I am running a paired samples t-test on the scores for 13 students who have gone through a school programme (before and after scores). My p value is 0.02 hence it is significant.

How do I get the effect size for the test? And am I right to say we only report effect size if the results are significant?

I also noticed there is a Pearson Correlation reported in the t-test table, stated as 0.796.. Would that be an effect size for my t-test?

Thanks very much!

October 20, 2021 at 4:07 pm

The effect size for a paired t-test is the difference between the means. In your case, that would be the mean of the 2nd test minus the mean of the first test. Typically, the null hypothesis value is that the mean difference is zero. Because your p-value is statistically significant, you can reject that null hypothesis and conclude that a non-zero effect exists in the population. The mean difference is the point estimate of the population value. You can also calculate a confidence interval to find a range of values that the population effect is likely to fall within.

Correlation is a different type of effect. In your case, it determines whether above average scores on the first test correspond with above average scores in the post test. That provides a different type of information than the mean difference. You can have a positive correlation between tests, like you do, even if there is no improvement between the 1st and 2nd test. There’s a p-value associated with Pearson’s correlation to determine whether it is significant.

You might be interested in my post about effect sizes .

I hope that helps!

' src=

October 13, 2021 at 2:27 pm

Hey Jim, I just bumped into your website and I have to say your arcticles are great – I definitely gonna follow you. I have one question regarding the t-test though:

I fully understand the one sample t-test where you divide the mean difference by the standard deviation of the sampling distribution where the latter is calculated by dividing the standard deviation of the sample by the square root of the sample size = s/SQRT(n).

BUT … when it comes to the two-sample test I don’t really get the hang off it – my question: why do you sum up the TWO standard deviations of the TWO samples in the denominator instead of taking the average?

I hope my question makes sense to … many thanks in advance!

October 13, 2021 at 4:59 pm

Hi Alexander,

Thanks so much for your kind words and welcome to my website! 🙂

There’s two forms of the t-test. One form uses the pooled standard deviation, which is essentially an average over the two samples. It’s really the standard deviation that combines the data for the two groups into one group. That works fine when the two groups have relatively equal variability. In fact, using the combined sample size to calculate the pooled standard deviation gives you a bit of extra statistical power to detect differences because you obtain a more precise estimate of the variability.

However, when the standard deviations for the groups are different, you shouldn’t average or combine them because the average or overall variability won’t accurately represent the variability of either group. In that case, you need to use each group’s standard deviation.

' src=

August 5, 2021 at 7:55 am

Hi Jim I appreciate your tutos. My question is, if I have to compare two programmes A1 and A2 on same input data, I used paired t-test and the output p-value is 1.1% assuming alpha=5%. That means A2 is better than A1. Now if I change the column order A2, A1, we get the same p-value, that means A1 is better than A2. I am confused here.

August 7, 2021 at 12:02 am

Paired t-tests analyze the differences between two columns. Consequently, if you change the order of one column, you’ll change the differences between the columns and, hence, the results. I’m guessing that’s what happened. You’ve reordered the columns in such a way that didn’t preserve the fact that each row needs to correspond to one item.

For more details about how paired t-tests work, read my post about t-tests .

' src=

July 10, 2021 at 1:39 am

Very descriptive and easy to understand. The examples really helped. Thanks Jim!

July 12, 2021 at 5:33 pm

You’re very welcome, Nathan! So glad it was helpful!

' src=

June 15, 2021 at 8:36 am

Jim, happy to see that you’ve added video to your content!

I hope you continue to add more of it 🙂

June 19, 2021 at 4:19 pm

Thanks so much for the positive feedback! I will keep adding more videos!

' src=

June 9, 2021 at 9:29 am

Hi Jim, I realised that when I conducted a t-test two sample assuming equal variance, my t-stat value is negative, what could that suggest. The question is linked to “ Is there statistical evidence that the average price of houses with one bathroom is different than the average price of houses with more than one bathroom?”

June 9, 2021 at 11:07 pm

That’s not a problem and the conclusion is still the same. When you’re performing a 2-sample t-test you are subtracting the mean of one group from another like this: Group A – Group B = Difference

If Group B has a higher mean than group A, you’ll obtain a negative value. I bet for your data, Group B is the set of homes with more than one bathroom, which presumably will have a higher mean price.

To learn more about the t-value, how it is calculated, and what it is for, read my post about T-values and T-distributions .

June 7, 2021 at 6:57 pm

I understand now. Thank you very much. I appreciate the support you have given me.

June 7, 2021 at 12:22 pm

Oh I did not know that.I would have to reject the null hypothesis as that value is less than the 5% significance level. Would that mean there is evidence that both averages are different? Based on the question about “Is there statistical evidence…” I stated in my previous comment, I also did a F-test Two Sample for Variances.Is that still fine to do based on the question.

June 7, 2021 at 4:44 pm

Yes, when you have a p-value less than your significance level, you reject the null hypothesis. That’s usually a good thing! Your p-value is lower than any reasonable significance level, which means that your results are statistically significant. To answer your question, it indicates that there is sufficient statistical evidence to conclude that the average housing prices between the two groups are different.

The Variance F-test answers a different type of question. It assesses whether the variability (rather than the mean) is different between the two groups. For more information, read my post about using Excel to analyze variability . If the question you’re answering specifically asks about the means prices, then you don’t want to use the Variances test because it’s answering a different question.

June 4, 2021 at 3:32 pm

Hi, I am seeking some assistance, A question states “Is there statistical evidence that the average price of houses with one bathroom is different than the average price of houses with more than one bathroom?”. There are 321 observations and I conducted a t-test two sample assuming equal and unequal variances. Both test shows a negative P value(two tailed) for example, was “1.3502E-14”. Is there a solution you could provide?

June 6, 2021 at 8:30 pm

There’s no such thing as a negative p-value. P-values range from 0 to 1. Oh, I see the problem. What you’re seeing is not a negative value, it is scientific notation, which is used for very large and very small numbers. In your case, it’s a very small number, which is good because it’s a p-value!

The number to the right of the E (-14) tells you how many places to move the decimal point. The negative value indicates you need to move the decimal point to the left by 14 places.

It’s equivalent to: 0.000000000000013502. In other words, it is very significant!

It sounds like you’re performing the correct analysis. You need to perform a 2-sample t-test (independent samples), two-tailed. Assess the variation to determine whether to use the equal or unequal variances.

' src=

May 29, 2021 at 9:25 pm

Hi Jim, I’m using 2-sample t-test, so your reply relieves my worry again. Now I can decide what to do with my samples with confidence. Thanks a lot! ^_^

May 29, 2021 at 9:26 pm

You’re very welcome! Glad to help!

Best of luck with your analysis!

May 29, 2021 at 9:00 pm

Thanks a lot, Jim. However, there’s another situation confusing me. Consider two groups of observations, each comprising of more than 50 observations, but the exact numbers of observations in each group are not equal. If t-Test is to be carried out in such situation, what kind of additional work should be done? Or that t Test isn’t capable of such situation at all? Thank you.

May 29, 2021 at 9:15 pm

2-sample t-tests with independent samples are perfectly able to handle unequal numbers of observations per group. Paired t-tests use dependent samples and assess the differences between paired observations, so the groups must be equal. I’m not sure which type of t-test you’re using.

If you’re using a paired t-test, you’ll need to remove the items/subjects that have only one observed value. Include only the items with two observations.

If you’re using 2-sample t-tests with independent samples, you don’t need to do anything when you have unequal sample sizes. The test performs fine with unequal sample sizes. However, if you’re planning to conduct a 2-sample t-test, you’d prefer to have equal sample sizes to maximize statistical power. For example, if your limited to 100 observations, you’d like to split those 50/50 to get the most power. If you split it 60/40, the test performs just fine without adjustments, but the power will be slightly less than if it was 50/50. But there is no requirement for equal sample sizes and there is nothing to do if you find yourself with unequal sample sizes.

May 29, 2021 at 6:22 am

Hi Jim, You tutorials help me a lot, thank you. As you explained, t-Test works well with samples comprising of 20 or more observations. However, I wonder would t-Test still be a good choice for samples comprising of more than one hundred observations? Or if there would be other fine technics suits such situation?

May 29, 2021 at 8:28 pm

I’m so glad to hear that they’ve been helpful!

No, there’s not really a better method with even more observations. Having more observations increases the statistical power of t-tests as well as increasing its robustness to departures from normality. Also, the width of the confidence interval will be narrower as you increase the sample size, which means you’ll have a more precise estimate of the effect size. So, you don’t need a different test for larger sample sizes, but larger samples improve the t-test and CI!

' src=

May 6, 2021 at 6:04 am

Hi Jim, i had a question about reporting the independent sample t-test results. I have reported the significance and reported the differences in the group means, But are we also suppose to report the assumptions as well? like “Levene’s Test of Equality of Error Variance showed a non-significant result, thus the assumption of homogeneity of variance was found and equal variances were assumed”? is this important to report ?

May 9, 2021 at 9:31 pm

Yes, that’s entirely acceptable. You can report the Levene test results as support for your decision to use equal variances.

Just as a caution, when you have a small sample size, it’s possible to obtain insignificant Levene’s test results even when the variances are not equal. That occurs because with a small sample size, Levene’s test loses statistical power (along with the t-test). Just something to be aware of with small sample sizes!

Also note that they don’t have to be exactly equal. Problems don’t start to occur until one variance is about twice the other.

' src=

May 3, 2021 at 1:25 pm

Adding onto this question I am also facing a similar issue – I’ve got 2 groups (n=50 & n=6) but the output on excel states df=7. I have used your calculation above and got an answer of 29. In this case is it still appropriate to be trusting excel? and if excel has got the df wrong, does this affect the t-stat or p-2-tailed value.

May 3, 2021 at 9:20 pm

Hi Rehana, it depends if you’re assuming equal variances or not. And, it depends on what the actual variances are too. Consequently, I can’t tell from what you write whether Excel calculated it correctly. The degrees of freedom definitely affect the t-value and p-value!

April 29, 2021 at 7:05 am

If I am comparing 2 variables with very different sample sizes (n=50 and n=6), should I do a test assuming unequal variances even if the variances are similar (0.058 vs 0.032)?

April 30, 2021 at 12:21 am

You don’t need to assume unequal variances because of the difference in sample size. However, one your variances is nearly double the other. That’s when you start to worry about the effect of unequal variances. Consequently, I would assume unequal variances for that reason.

' src=

April 15, 2021 at 4:47 pm

I am using this very helpful tool – thank you for your explanation. However, I am a bit worried about the degrees of freedom (df) outputted. I am conducting a 2-tailed t-test assuming unequal variances for 2 samples; “High” and “Low”. Each group has 130 observations included which I assume implies 258 degrees of freedom. However, the output I get for each test (20+ different pairs) varies as I have received df-values from everything between 223 a 258. Should I disregard this or? I believe you also show an output of a t-test of 2 groups, each with 15 observations, but with a df = 14….

Best regards, Oliver

April 15, 2021 at 4:56 pm

The df gets more complicated for unequal variances. The equation is below for a 2-sample t-test with unequal variances. But I would just trust the output!

Equation for the degrees of freedom for a 2-sample t-test with unequal variances.

April 13, 2021 at 1:43 pm

Hi, i am seeking some advise. I have done SEC on two differently treated samples, with 20 fractions analysed via NTA. so 20 fractions from method 1 and 20 fractions from method 2. I am wanting to compare the significant between a specific fractions and the different methods, So say fraction 6 from method 1 and fraction 6 from method 2. Is the correct thing to do is a one sample T test ? Thanks

April 15, 2021 at 4:27 pm

Hi Rose, if I understand correctly, you’re comparing two samples, so you’d need a 2-sample t-test. However, if you’re comparing multiple groups, you’ll need to use a post hoc test to control the family error rate .

' src=

April 5, 2021 at 4:44 pm

Yes, that data is just one column with years 2010-2019, and a second column labeled “Difference in Vehicle Accident Rates: Flip flops Minus All other Footwear”. Sorry my phone made it “slip shoes” in the last post and I didn’t notice. By range, I was just meaning the rate differences in the second column are from 3% to 9%. For more context, we are conducting a very basic multi-discipline case analysis. A lawsuit involving a vehicle collision where the plaintiff is suing the other driver, who was wearing flip flops. The plaintiff has obtained this survey data and there is a question as to whether it can be meaningfully analyzed to support a claim of negligence against plaintiff or possibly manufacturer. So the business law side is a separate component obviously, but I don’t see how to do hypothesis testing with this data. The assignment prompt is pretty vague with regards to statistical methods. The only thing I can think of is maybe regression analysis to show that more flip flop driving accidents are occurring each year, and therefore the manufacturer may need to consider product warnings to avoid strict liability.

Data is: YEAR/ ACC. DIFF

2010 4% 2011 5% 2012 3% 2012 4% 2014 6% 2015 5% 2016 7% 2017 8% 2018 7% 2019 9%

I can derive a mean, standard deviation, and t-stat, and probability from this info, but would they really be valid given how the data is presented to us?

April 5, 2021 at 9:22 pm

That’s a tricky dataset given the lack of details. We don’t know the raw counts and sample sizes for both groups for any of those years. If you had more details about the raw data, you might be able to determine whether flip flops had more accidents. This is really more of a proportions problem than a t-test problem. However, using a normal approximation, you might been able to do something along those lines with a t-test. However, proportions close to 0 or 100 are less likely to approximate a normal distribution. A large sample size can help, but we don’t know that either.

Also, whether you’re using a t-test or proportions test, the parameter in question (mean or proportion) has to remain constant over time. It doesn’t look like that’s the case.

It does appear that the difference between flip flop accidents and other shoes is increasing over the years. However, again, not knowing how the data were collected, sample sizes, locations, methodology etc., it’s not safe to draw those conclusions.

Finally, even if we could somehow trust the data and conclude the difference is growing, that still leaves us with a key unanswered question. Are flip flops causing the accidents? The data might be showing a correlation, but that doesn’t imply causation.

Of course, if the data are presented by the plaintiff, he’s not unbiased. We have no idea if the data are valid. Even if he didn’t intentionally bias the data, there are so many questions about the specifics, I’d say it’s impossible to draw any sound conclusions. If you trust the data, you might be able to say that the difference is growing over the years. But, without the raw data, you can’t apply any hypothesis test. Sample sizes might change over the years. Data collection locations might change. Etc. I’d imagine that if this was used in court, it would be ripped to shreds unless it was part of a published study. Any published study would have all those necessary details.

Maybe the point of the assignment is to know when not to use data because it can’t answer your question? Or, maybe you need to access the survey that you mention to verify all of those data and methodology questions?

April 3, 2021 at 7:41 pm

I just ordered your ebooks and I really appreciate you taking time to help others with their questions. Regarding determining the the appropriate statistical analysis, I am a bit stumped by a problem I am working on. For reference, I am a college sophomore studying business applications, so advanced analyses won’t apply. I am unsure how to approach a statistical analysis when very limited data is given. For example, I am provided only a table with two columns: years (n=10) and “difference in accident rates: slip shoes vs all other shoes”. I’m being asked to interpret the data. The rates are given as percentages, so I know the data is continuous; the range is (9-6=3), so I know there are no outliers and we can expect normal distribution. This seems like a paired t-test would be appropriate, except I’m confused because instead of being given separate data for slip shoes and all other shoes, I’m only given the differences. I’m not sure how I could model this in excel, although I have made basic calculations such as the standard deviation and mean. Any advice to point me in the right direction on analysis methods would be appreciated. Thanks for your time.

April 4, 2021 at 8:11 pm

I’d guess that the original data were binary, accidents versus no accidents. From that you’d get the proportions/percentages.

When you say the range is 3, I don’t know that’s referring to. Range of what?

Sounds like you need to do a 2-sample proportions test. Although, that would be hard to do without the underlying data. They’re showing you the difference in accident rates over time? And, you have one column for each year that is the difference in accidents as a percentage?

It’s hard for me to picture, so I’m having a difficult time coming up with recommendations. Do you think they’re asking you to look for trends over time?

' src=

April 1, 2021 at 10:00 pm

Hi Jim, thanks for the info. I might have a pretty straightforward question but can’t find any specific answer and was hoping you could help. I have a number of groups that I am comparing with a t-test assuming equal variances, due to the multiple comparisons a Bonferroni adjustment is required. Here I know for my data it is alpha/45 = 0.00111.

My question to you is, in using the data analysis package for the t-test in excel the alpha is 0.05 (no problem there) but it can be changed (here you stated that we should try and avoid changing it), when I do this with my 0.00111 bonferroni adjusted p-value the results are the same as with an an alpha of 0.05, so what is the point in changing alpha here? In addition, is it possible to do the bonferroni like this, or would it be better to two the t-test under alpha = 0.05 and then do the bonferroni adjustment ad hoc?

I look forward to hearing from you.

Kind regards, Dylan

April 1, 2021 at 11:36 pm

If you’re changing your alpha based on the Bonferroni correction, it makes sense that the rest of the results stay the same. That’s because the significance level is something that you as the researcher sets. It doesn’t enter into the calculations at all. To learn why, read my article about understanding significance levels . I’m not really sure why Excel asks for alpha.

What you need to do is compare the p-values to your alpha. With a lower alpha, the p-values must be lower to be significant. That can change which group comparisons end up being statistically significant. So, it is potentially changing the results in that sense. But, Excel’s output doesn’t change. And, when I say that an alpha of 0.05 is generally a good one to use, I mean for a single test and not multiple comparisons.

45 groups is a large number! As you’re seeing, that dramatically lowers your effective alpha for each comparison. Additionally, Bonferroni is a conservative procedure in that it plays it extra safe, but that causes you to lose statistical power.

Your case is one where Excel’s limitations might be a problem. I’d recommend reading my article about using post hoc tests . There are other comparison procedures that aren’t as conservative and if you don’t need to compare all pairwise groups, that can help too. I discuss all that in that other post. It might give you some ideas. On the other hand, if you’re happy with results you’re getting using Bonferroni for so many groups, it might not be a problem! I write about these post hoc tests in the context of ANOVA, but the ideas apply for multiple comparisons even if you don’t perform the ANOVA test.

I hope this helps!

' src=

March 11, 2021 at 11:07 am

I’ve collected electrical conductivity values of water samples at a site I’m doing a study at and would like to know which statistical test would be appropriate to use please. I have taken readings from 3 sites, and at 4 times in the day corresponding to low tide, high tide, and two intervening periods equally spread out within that. At each site and time of the day, I took two readings which I averaged to get a mean. I would like to know whether the values significantly differ at a certain site (where I have 4 mean readings), so I can say that the tidal impact on electrical conductivity is negligible because this test shows that at site 1, 2 and 3, they don’t vary significantly. I hope this makes sense!

' src=

February 22, 2021 at 3:41 pm

February 21, 2021 at 8:46 am

Hi Jim, Could you help me on my data interpretation for a t-test. I am confused to whether I have one or two tailed distributions and then whether my results are paired, homoscedastic or heteroscedastic data.

I am evaluating pollution change from 2019 to 2020 due to the covid lockdown. I have pollution data from the same time period 23/3 – 30/4 for 2019 and then 2020, for one pollution type for example nitrogen dioxide data.

Thank you in advance Cerys

February 22, 2021 at 3:18 pm

I have several posts about whether you should use a one or two-tailed test. I’d recommend reading those and then if you have more detailed questions, post them there. Typically, use a two-tailed test unless you have a strong rationale for using a one-tailed test. The posts describe what constitutes a “strong rationale.” One-tailed and Two-tailed Tests Explained When Can I Use One-Tailed Tests?

As for the equal variances assumption, you can do a formal variances test in Excel . However, if you have a very large sample size, the test will detect unsubstantial differences in variances that won’t affect your results. The general rule of thumb is to look at the standard deviations and if one is twice (or more) the size of the other, use Welch’s t-test which does not assume unequal variances. In fact, if you’re not sure, just use the unequal variances version and know that you can trust the results. You only lose a little bit of statistical power.

' src=

February 20, 2021 at 10:44 am

Excellent! Thank you, Jim. I will check out the pages suggested by you.

February 9, 2021 at 12:13 pm

Jim, please do check the question during your free time.

February 11, 2021 at 5:43 pm

Hi Prasanth, sorry for the delay. I’ve replied to your earlier comment!

' src=

February 8, 2021 at 5:08 am

Just got a quick questions cus im kinda confused right now. Im doing a study on The Effectiveness of Face-to-face Classes and Remote Learning among University Students. I honestly have no idea what to do nor what my Ho and Ha should be. I did a descriptive survey with questions about how effective is face to face learning and another set of questions for how effective is remote learning to gather data. I was wondering what the variables should be for x and y and what kind of test i should do cus im tasked to do a hypothesis testing on one population mean.

February 11, 2021 at 5:01 pm

It’s hard for me to answer some of your questions because I don’t know the type of data you are gathering. However, here’s one possibility.

If you measured effectiveness for in-person and remote classes using a continuous outcome variable, you could compare the mean effectiveness between the two groups. Your hypotheses would be the following:

H0: the mean in-person and remote effectiveness are equal. HA: the mean in-person and remote effectiveness are not equal.

You could then use a two-sample t-test to compare the means of the two groups. If your p-value is less than your significance level, you can reject H0 and conclude that the results are statistically significant. Significant results would indicate that your sample provides sufficient evidence to conclude the population means are different.

' src=

January 30, 2021 at 11:15 pm

sir if i have 2 groups one with diet and one with diet and yoga, and i want to compare their blood pressure and bmi before and after then what statitical tool should i use

January 31, 2021 at 2:15 am

Hi Remya, sounds like you need to use repeated measures ANOVA.

' src=

January 16, 2021 at 1:17 am

Thank you so much Jim for taking the time to reply to me.

Let me try to explain what I aming for. Let’s assume I have a group of 500,000 customers who hold a specific financial instrument. Out of those customers, 100,000 also hold another financial instrument. I want to test that the 100,000 customer group holding the 2 instruments (hence the uneveness of the two sample sizes), have a higher income than the rest 400,000 customers.

Is the Two-Sample t-Test the right test for this study?

Should I perform an F-test before proceeding with the Two-Sample t-Test?

Or, should I randomly select a 100,000 sample from customers with one instrument only to make the two groups even in sizes?

Thank you very much in advance Jim.

January 16, 2021 at 11:19 pm

Be sure that your groups are independent (different people, no people in both groups). For your scenario, you have 500,000 people. You’d split them so that the 100,000 with the other instrument are in one group and the people who don’t have it (400,000) are in the other. The 2-sample t-test is valid.

If you’re unsure about have equal variability between the groups, I actually would not recommend the F-test for such a large sample size. That test will have so much power with such a large sample size that even a trivial difference in variability will be statistically significant. Basically, a false alarm. Instead, just compare the standard deviations. If one standard deviation is twice (or more) the size of the other, then you have problems. And, if you’re unsure, you can perform the form of the t-test which doesn’t require equal variances.

Finally, your theoretically supposed to draw a random sample from a defined population. The results apply to the population that you drew the sample from. I’m guessing from what you wrote that is not how you acquired your data. Existing customers. You’ll have to evaluate how well it approximates a random sample from some population.

January 15, 2021 at 7:18 am

Hi Jim, this is extremely useful. Is there an issue with the reliability of the results when performing a Two-Sample t-Tests with uneven sample sizes? i.e. 87,000 vs 400,000. Thanks

January 16, 2021 at 12:46 am

It’s fine to use different sample sizes. The results are still valid. If you’re planning a study and want to maximize your statistical power, you’ll get the maximum power if you split the total sample size in half. For example, if you want a total sample size of 100, you’ll get maximum power for a 2-sample t-test if you split it 50/50 between the two groups.

However, if you’re working with an already uneven split, that’s no problem. And, with such large sample sizes that you have, you probably won’t have problems with low power.

There’s one potential snag you should think about. Why are the sample sizes so uneven? If it’s because subjects dropped out unevenly, you have the potential for selection bias if they dropped out in some manner related to the outcome. That’s not an issue with the hypothesis test but rather the underlying data.

' src=

January 14, 2021 at 9:23 pm

Hi Jim, Thank you for such handy explanations! I am a fairly amateur with stats, with just a single university unit under my belt. I am unsure how to approach the following data.

We have a collection of data with 4 different grocery store chains, measuring the brix (sugar level) and pressure of peaches form each shop over 8 weeks of the season. How do I go about testing the change data from the 4 different shops over 8 weeks. We would also like to see if there is a relationship between sugar and pressure, but I assume this is too many variables for a single test route, so would split into two tests.

Please note, my head is in a rabbit hole at this point, so I may be blind to some very obvious errors i’m making!

Hope you can help, cheers.

' src=

January 12, 2021 at 4:21 pm

Hello, Jim, first thank you for creating such a valuable resource. My question is this: Lets say I have three variables in each column: name, gender and height (in stacked column format, so i have one row of data for each unique value: e.g Dave, Male, 23 and then next column Sara, Female 31 and so on).

I want to compare if the mean height of men and women are statistically different in my data. How can i do that using excel. Can this be done? How?

Thank you and wishes for 2021.

January 14, 2021 at 2:16 pm

There are several things to consider about t-tests. For one thing, you can have at most only one categorical variable, such as gender. And, that categorical variable can only contain two groups. For gender, you can compare the mean of some characteristic between males and females. When using t-tests, you are not able to include a continuous variables, such as age.

It sounds like you need to use regression analysis, which will allow you to include both gender and age in the statistical analysis. Read my post about when to use regression analysis for more information. Also, I’ve written a post about how to perform regression analysis using Excel .

' src=

December 21, 2020 at 12:40 am

Great! Thanks so much for your help

December 20, 2020 at 8:07 pm

Hey Jim, thank you for the page – I have a doubt – Why can’t we use Confidence.t function in Excel to calculate margin of error for differences of two means of independent populations. As I understand, confidence.t function can be used to calculate margin of error for difference of two means for matched pair samples.

Hi Prasanth,

Sorry for the delay, I looked into the function. The Confidence.t function is designed to calculate the confidence interval for a single sample. In other words, you have one column of values and you want to calculate the CI for that column.

Yes, you can use this function to calculate the CI for the differences between paired samples. The reason this works is because a paired t-test is equivalent to a 1-sample t-test on the paired differences. The calculations are the same, which means it is ok to use the same functions.

However, it is correct that you CANNOT use it for the difference in means between independent groups. That’s because, unlike the example above, a 1-sample t-test is not equivalent to a 2-sample t-test. The calculations are different and you, therefore, can’t use the same commands.

For more details, look at my other post about t-tests and t-values , which illustrates the equivalence between paired t-tests and 1-sample t-tests. Note how 2-sample t-tests (independent groups) are different.

' src=

December 19, 2020 at 7:35 am

Hi Jim. Im getting a letter in my t-test result: 2.84997767514918E-06

What does this mean please?

December 19, 2020 at 7:03 pm

That is scientific notation, which is used for very large and very small numbers. In your case, it’s a very small number, which is good if it’s a p-value!

The number to the right of the E (-06) tells you how many places to move the decimal point. The negative value indicates you need to move the decimal point to the left by 6 places.

It’s equivalent to: 0.000002849. . . .

' src=

December 2, 2020 at 8:02 am

What do I put in the Hypothesized Mean Difference field though if the claim is that there is a difference in the means (the first one is greater than the second)?

December 3, 2020 at 1:55 am

Hi Kelly, I cover this in step 4 of the procedures.

' src=

December 1, 2020 at 9:47 pm

Hi Jim, I have a problem with my data that is i only have the sum of the samples without specific value for each sample, how should i perform it in excel? Can you help me, please? My data: 6241 worms / 30 samples. Thank you!

December 1, 2020 at 11:33 pm

You’d need to tell me more about the goal of your analysis. If you data comprise counts of worms for each sample, you might need to perform a Poisson rate test or Poisson regression. But, I’m not sure what you want to learn from your data.

' src=

November 9, 2020 at 12:30 pm

Hi Jim!!!! Thank you for the easy to understand explanation. I have used the t-test before but never truly understood it or when to use it the way I do now!

November 10, 2020 at 2:19 pm

Hi Stacey, thanks for writing! I’m so glad to hear that this post was helpful!

' src=

October 15, 2020 at 2:02 pm

Hi Jim. This is an excellent explanation. Thank you so much.

I was wondering if there is a way to calculate the standard deviation from the information that results from the excel output?

Thanks. Valodia

October 15, 2020 at 2:24 pm

Hi Valodia,

Yes, you can! The standard deviation is just the square root of the variance. So, all you need to do is look for variance in the output and take the square root of it to find the standard deviation. For example, if you look in Excel’s output for the 2-sample t-test, you’ll see variances for each group and the pooled variance. Take the square root of those variances to find the standard deviation for each group and the pooled standard deviation, which is for all the data.

' src=

September 3, 2020 at 3:50 am

Thanks for your comments Jim!! I ended up doing a 3 way ANOVA. I thought to share my results with you. Might interest you. Worked like a charm!!

September 3, 2020 at 10:29 pm

I’m so glad it worked like a charm!! I had to remove the output because it was so long. But, that’s great!

' src=

September 1, 2020 at 10:54 pm

Hi Jim, I am a newbie in discovering the wonders of Excel in calculations. I am in a dilemma now. I have this questionnaire with 4 dimensions – so lets say for Dimension A, I have 7 questions, Dimension B (11 questions), Dimension C (10 questions) and Dimension D (7 questions). Now I would like to see the relationship between gender and dimension as a whole. Now, my questions are (1) How do I group the 7 questions in Dimension A as one variable? (2) How do I group all the questions in the 4 dimensions as 1 variable? Thank you Jim.

August 27, 2020 at 3:24 am

Holy heck Jim no one has ever replied to me on any other stats message board. Thanks. I want to pick your brain a little bit more. Below will be the structure of the data. Such data will be collected for 4 weeks at two temperature each. Can i do a one factor anova for one week data and compare if the formulations are different? At the end of the study I will take each formulation at different weeks and do another anova to check how formulations behave over time. Is there a simpler way?

Week0 F1 F2 F3 F4 F5 F6 F7 F8 Outcome-Repeat1 x x x x x x x x Outcome-Repeat2 x x x x x x x x Outcome-Repeat3 x x x x x x x x

August 29, 2020 at 3:43 pm

I don’t see why you couldn’t do it that way. However, you’re throwing away valuable information by not including the week 0 (i.e. pretest) information. That would be my concern. If you have that data, you might as well use it in a repeated measures design.

If you go the route of a one-way ANOVA at week one, that would tell if the formulations have different group means at that point. However, that test will have less power than the repeated measures design including the pretest scores.

So, can it be done? Yes. It’s a simpler model too. But, you lose some statistical power. My upcoming post is about independent and dependent groups, which gets into repeated measures designs and the issues involved.

August 26, 2020 at 5:52 am

Hi Jim!! Big Fan. I have the following problem. Would like to know what you think

I am screening different liquid formulations for a drug. There are 8 unique formulations studied at two different temperatures. The aim is to identify which formulation is best with respect to stability. I was planning to do an ANOVA for the 8 different formulations to see if the formulations are different from each other and then do a pairwise t test at the two temperatures as it is essentially the same sample at two different temperatures. Is this a good approach?

August 26, 2020 at 3:34 pm

It sounds like you need to perform a repeated measures ANOVA, which is an extension of the paired t test. You’d include Formulation, Temperature, and Time in your model. You’d be particularly interested in the interactions between Formulation*Time (does the relationship between formulation and your DV depend on time). If that term is significant, then you know the formulations behave differently over time. Also, you could include temperature in that interaction. Three-way interactions are rare in analyses, but your model might call for it. It would answer the question whether the relationship between Formulation and the DV depends on both time and temperature.

Additionally, you can perform post hoc analyses on these interactions terms so you can control the familywise error rate for all these comparison. You don’t get that benefit if you use a series of paired t-tests.

' src=

July 28, 2020 at 9:08 am

how we will tell that mean of one variable is greater than the other variable in two tailed t test

July 29, 2020 at 12:27 am

There are two ways. One is just to look at the two mean values. The output should display the mean for each group. That’s the easiest way. For example, if the mean of group 1 is greater than group two, and the results are significant, then you know that the mean 1 is significantly larger than mean 2. You can also look at the t-values and then link that to critical areas and how that relates to your hypothesis test.

But, I’d just look at the group means!

' src=

July 10, 2020 at 5:03 am

Hi Jim. Could you please clarify if t-test can be used if data have a number of outliers, i.e. distributions are heavily skewed? Many thanks

July 12, 2020 at 5:47 pm

Hi Natalia,

T-tests are not recommended when you have outliers that skew the distributions. A few values can dramatically change your results. Consider using a nonparametric test instead. I talk about this in my post about parametric vs. nonparametric tests . It’s under one of the benefits of using nonparametric tests.

' src=

July 9, 2020 at 11:44 pm

I am trying to assess the impact of an intervention in a sample of 30 participants pre and post intervention using a set of clinical outcome measures sample before and after the intervention. As the intervention was applied at different times for each participant, I have collected different number of pre and post intervention outcome measures. To clarify: I have 2 pre-intervention outcome measures and only one post intervention measures for participant X while I have 3 pre-intervention and post intervention measures for participant Y.

What is the best way to do a paired sample T test in such a case? For some participants, there is no corresponding post-intervention outcome recorded as the prescriber did not request for the clinical test. Do I just choose the last recorded pre and post intervention values or do I calculate an average of pre and post intervention measures for each participant? If I am to calculate the average, then how do I account for a single or no outcome value?

I’d appreciate your advice on this.

July 9, 2020 at 5:27 am

Your website has been a heaven sent for me! My knowledge of statistics is rudimentary and I cannot thank you enough for the simple way in which you explain concepts. I am currently writing up my thesis and I had a question about selection of variables in a paired sample t-test. My research looks at the impact ( if any) of having a pharmacist involved in the care of people with type 2 diabetes and I have clinical parameters measured pre and post intervention. I also have a control group who have been exposed to usual care.

My question is – does it matter in what order I choose my sample variable when I do the paired t-test ( using XLSTAT). I have added pre-intervention means as variable 1 and post-intervention as variable 2. Would the values change if I conduct the test choosing post intervention as variable 1 and pre as variable 2?

Apologies in advance if this is a silly question but I just want to be sure I am on the right path here.

Once again, many thanks.

Cheers Geeta

' src=

July 7, 2020 at 12:52 pm

July 7, 2020 at 12:51 am

Hey sidra .you find the answer yet or not.i thnk tour student of b.ed i am also looking for ths answer

July 7, 2020 at 12:57 am

Hi, I answered Sidra’s question in the comments. You might need to scroll down.

' src=

July 5, 2020 at 6:41 pm

Thank you Jim. your answer was really helpful.

July 5, 2020 at 5:38 pm

Hi jim i am a new student of statistics. I really need to solve question. Can you give me any idea which test will be suitable for this research. “A researcher conducted a research that majority of the people who died during pandemic bought a new phone during last year.If his assumption is correct which statistical test should be appropriate “

July 5, 2020 at 5:49 pm

It sounds like you need to perform a two proportions test. You need to determine whether the proportion of pandemic deaths is different between the group that bought a new phone within a year versions those who did not. Caution: For these data, you wouldn’t be proving that buying phones caused pandemic deaths. Instead, you’d be showing a correlation (not causation) between them.

In my new ebook about hypothesis testing , I cover proportions tests in great detail.

' src=

July 3, 2020 at 8:01 am

I am conducting a quantitative research study. The hypothesis is does Variable 1 (intervention) impact Variable 2 (posttest scores). I have a control group and an experimental group and pretest and posttest scores for all participants. I have entered all scores as 1 data set in excel and from my understanding should use the paired mean t-test? But then how do I compare the groups? For example how do I find the mean for the experimental group separately from the control group? Or the separate standard deviations? When reporting results would I use the one p-value calculated for the total group for the null hypothesis testing?

July 3, 2020 at 5:06 pm

Based on what you write, it sounds like you’ll need to use repeated measures ANOVA rather than a t-test. You have pre-test and post-test scores and you have two experimental groups. That’s four groupings based on two independent variables, time and experimental group. T-tests can’t analyze that type of design.

To answer your question about whether the mean difference between pre-test and post-test scores depend on group assignment, you’ll need to include an interaction term for time*group in your repeated measures ANOVA model.

' src=

June 22, 2020 at 2:18 pm

I am attempting to run a t-Test comparing time to complete a task where the pre- and post array data each have 70 days. However, in the post data there are days when the task didn’t occur. Is the correct way to represent this within the array to include a 0 in each of the lines, or to leave it blank? I have run it each way and in this instance the p-value becomes >0.05 if the 0’s are included for time measures, but become significant if the 0’s aren’t included. However, I want to represent the data correctly regardless of the resulting significance.

Thank you for your help.

June 28, 2020 at 12:33 am

I’m assuming you’re using a pair t-test with a pre and post test time for each subject. In this case, if a subject doesn’t complete both the pre and post test, you need to exclude that subject from the analysis. You definitely don’t want to enter a zero for missing data because the analysis will interpret that as a time of zero.

I hope that helps. Best of of luck with your analysis!

' src=

May 20, 2020 at 6:31 am

Hi Jim! Your explanation helped me a lot to understand how a t-Test works! I have one question about multiple t-Tests. If I conducted a t-Test (4 groups 1,2,3,4 and tested 1&4 / 2&3) and ANOVA for all 4 together does this count as multiple Testing? Therefore a Bonferroni correction is needed? Thanks a lot!

May 21, 2020 at 10:41 am

Hi Kevin, yes, that definitely does count as multiple testing. You shouldn’t be using t-tests for that. Instead, conduct the ANOVA and then use a post hoc test . Click that link to read an article I wrote about them. Bonferroni is a type of post hoc test and that would be an acceptable one to use. However, I don’t usually recommend that particular test because it’s known to be more conservative (less statistical power) than other post hoc tests.

' src=

May 1, 2020 at 9:10 pm

Hi Jim (Great name btw!). I have a question about the one tailed P-Value that Excel reports. It specifically says “P[T <= t]" which if interpreted literally would be P[ T <= – 1.652] if the t test statistic were -1.652, and if there were, say, 13 df, then P[T <= -1.652] would be equal to 0.061235 (rounded off to the 5th decimal place). If, on the other hand, the t test statistic had been +1.652, then P[T <= 1.652] = 0.93877 (rounded off to the 5th decimal place), BUT Excel would still report, "P[T <= t"] as 0.061235. So my question is this: even though Excel prints "P[T <= t] one tailed", it's really calculating the left-hand tail probability if the test statistic is a negative number, and the right-hand tail probability if the test statistic is a positive number? Do I have that correct? Thanks (for the life of me I could not find the answer to this question out there on the "interwebs"!)

May 2, 2020 at 11:25 pm

Hi Jim! Yes, it is indeed a great name!

Those labels in Excel are wonky. My take is that they’re missing symbols for absolute values. I noticed this weirdness before but your comment prompted me to think it through for both one- and two-tailed cases. T are t-values in the t-distribution while t is from your sample.

For both one-tailed tests and two-tailed tests, I believe it should be: P(|T| ≥ |t|).

This indicates the probability of obtaining absolute t-values at least as extreme as the absolute value of the t-value your study obtained. That covers all the cases I think for both positive and negative sample t-values and one-tailed tests in either direction. Excel would include “one-tailed” or “two-tailed” after that label because that tells you whether that probability is distributed between two tails or just one tail.

–The other Jim

' src=

April 8, 2020 at 9:55 am

I would like to know if you happen to know which formula Excel are using when calculating the t-value in the unequal variance t-test? So sorry for my “danglish” 🙂

' src=

March 26, 2020 at 11:19 pm

Hi Jim, Super helpful, i need to create a box plot from my P value, how would i go about doing this. Thanks

March 29, 2020 at 3:20 am

Hi Lauren, I don’t understand what you mean by creating a box plot from a p-value. I do have a post that explains box plots . Perhaps that will help. If that post doesn’t answer your question, please comment in the boxplot post and provide more details about what you want to do exactly. Thanks!

' src=

December 13, 2019 at 11:35 am

Hi Jim. Thank you for the work you are doing here. I read this article to help me better explain how to decide which excel tool to use when for a t-test. It helped much! I was disappointed to learn that excel doesn’t do one sample t-tests… but maybe we have a ray of hope? I read another article you wrote about one or two or paired t-tests (first link in this article) and there you wrote “Here’s a secret! Paired t-tests and 1-sample t-tests are the same hypothesis test incognito!”

So, a question – can one, using excel’s paired t-test function, put sample data in the first column and the null hypothesis value, repeatedly, in the second column and “trick” excel into running a one sample t-test using the paired t-test function?

December 13, 2019 at 11:47 am

That’s a great question. If you enter your 1-sample data in one column and then enter all zeroes in the other column, that should do the trick. I haven’t tested that to know for sure but will try it when I can. I suppose there’s a chance that Excel might choke on that.

If that approach works, entering all zeroes for one column will test the hypothesis of whether your data are significantly different from zero. Entering a different value from zero should test whether your data are different from that value. For example, if you wanted to determine whether your data were significantly different from a mean of 5, you’d enter all 5s in the 2nd column.

As you’ve noted from the other post, paired t-tests simply subtract one observation from another for each subject/item and then performs a 1-sample t-test using those paired differences.

' src=

November 29, 2019 at 10:40 am

Thank you, Jim! Your explanation is very clear and it helps me a lot.

' src=

October 11, 2019 at 2:30 pm

Jim, I know you are an expert with Minitab. I recently learned that the test of two proportions have two ways to calculate the z score depending upon whether you indicate it use a pooled or separate estimate of p. Can you explain how I would decide that as the differences in results can be substantial. Also, I’m not sure which one is used in Excel.

' src=

September 29, 2019 at 5:50 am

A rather technical post but I’m sure this will be helpful for those mastering Excel.

September 29, 2019 at 1:54 pm

I do provide the step-by-step instructions for how to do this in Excel. However, I hope to have accomplished other goals in the process. For instance, there are several t-test to choose from–which one is appropriate. And, how to choose the correct p-value to use? What’s the correct interpretation? There’s also links to other resources of mine the explain the various other statistics you’ll see in the output. And, importantly, what can’t Excel do? Excel doesn’t elaborate on all of that. Hopefully, that information is useful to readers!

' src=

September 19, 2019 at 8:43 pm

Nice Explanation!!!!!!!!!!!

' src=

September 18, 2019 at 1:39 am

yess… it was so helpful.. thank you

Comments and Questions Cancel reply

The Complete Guide: Hypothesis Testing in Excel

In statistics, a hypothesis test is used to test some assumption about a population parameter .

There are many different types of hypothesis tests you can perform depending on the type of data you’re working with and the goal of your analysis.

This tutorial explains how to perform the following types of hypothesis tests in Excel:

  • One sample t-test
  • Two sample t-test
  • Paired samples t-test
  • One proportion z-test
  • Two proportion z-test

Let’s jump in!

Example 1: One Sample t-test in Excel

A one sample t-test is used to test whether or not the mean of a population is equal to some value.

For example, suppose a botanist wants to know if the mean height of a certain species of plant is equal to 15 inches.

To test this, she collects a random sample of 12 plants and records each of their heights in inches.

She would write the hypotheses for this particular one sample t-test as follows:

  • H 0 :  µ = 15
  • H A :  µ ≠15

Refer to this tutorial for a step-by-step explanation of how to perform this hypothesis test in Excel.

Example 2: Two Sample t-test in Excel

A two sample t-test is used to test whether or not the means of two populations are equal.

For example, suppose researchers want to know whether or not two different species of plants have the same mean height.

To test this, they collect a random sample of 20 plants from each species and measure their heights.

The researchers would write the hypotheses for this particular two sample t-test as follows:

  • H 0 :  µ 1 = µ 2
  • H A :  µ 1 ≠ µ 2

Example 3: Paired Samples t-test in Excel

A paired samples t-test is used to compare the means of two samples when each observation in one sample can be paired with an observation in the other sample.

For example, suppose we want to know whether a certain study program significantly impacts student performance on a particular exam.

To test this, we have 20 students in a class take a pre-test. Then, we have each of the students participate in the study program for two weeks. Then, the students retake a post-test of similar difficulty.

We would write the hypotheses for this particular two sample t-test as follows:

  • H 0 :  µ pre = µ post
  • H A :  µ pre ≠ µ post

Example 4: One Proportion z-test in Excel

A  one proportion z-test  is used to compare an observed proportion to a theoretical one.

For example, suppose a phone company claims that 90% of its customers are satisfied with their service.

To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service.

  • H 0 : p = 0.90
  • H A : p ≠ 0.90

Example 5: Two Proportion z-test in Excel

A two proportion z-test is used to test for a difference between two population proportions.

For example, suppose a s uperintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences.

  • H 0 : p 1 = p 2
  • H A : p 1  ≠ p 2

How to Change Axis Scales in Google Sheets Plots

Statistics vs. analytics: what’s the difference, related posts, how to create a stem-and-leaf plot in spss, how to create a correlation matrix in spss, how to convert date of birth to age..., excel: how to highlight entire row based on..., how to add target line to graph in..., excel: how to use if function with negative..., excel: how to use if function with text..., excel: how to use greater than or equal..., excel: how to use if function with multiple..., how to search for a question mark in....

ExcelDemy

How to Do a T Test in Excel (2 Ways with Interpretation of Results)

Md. Meraz al Nahian

Download the Practice Workbook

T Test.xlsx

T Test Types

There are two types of t-tests. They are:

  • One-tailed t-test
  • Two-tailed t-test

Each of them has 3 subtypes. They are:

  • Two sample equal variance
  • Two sample unequal variance

How to Do a T-Test in Excel: 2 Effective Ways

Method 1 – using the excel t.test or ttest function for a t-test, case 1.1 – two sample equal variance t-test.

In the dataset, you will see the prices of different laptops and smartphones. Here is a formula that performs a T-Test on the prices of these products and returns the t-test result.

=T.TEST(B5:B14,C5:C14,2,2)

Calculating Two Sample T-Test Result by Formula

We set the 3rd argument of the function to 2 as we are doing a two-tailed t-test on the dataset. The 4th argument should be 2 for a two-sample equal variance t-test.

Case 1.2 – Paired T-Test

We are going to apply another formula to calculate the Paired T-Test . The following dataset shows the performance mark of some employees in two different criteria.

=T.TEST(C5:C13,D5:D13,2,1)

Calculating Paired T-Test Result by Formula

Note: The explanation of the results is described in the following sections.

Method 2 – Using the Analysis ToolPak

  • Go to the Options window.
  • Select Add-ins and click on the Go button in the Manage section.

Initiating Analysis Toolpak Add-in

  • The Add-ins window will appear. Select Analysis ToolPak and click OK again.

Adding Analysis Toolpak Add-in

Case 2.1 – Tw-Sample Equal Variance T-Test

  • Click on the Data Analysis button from the ribbon of the Data tab.
  • The Data Analysis features will appear. Select t-Test: Two Sample Assuming Equal Variances and click OK .

Opening Two Sample T Test by Analysis Toolpak

  • Set up the parameters for the t-test operation. Insert the Laptop and Smartphone prices as Variable 1 Range and Variable 2 Range. Include the headings in the range and check Labels.
  • Set the value of Hypothesized Mean Difference to 0 .
  • Select an Output option of your preference and click OK .

Setting up Parameters for Two Sample T-Test

  • As we have chosen a New Worksheet for the outputs, we will see the results in a new sheet.

Showing T-Test Result for Two Sample Test

Comments on Results

The output shows that the mean values for Laptops and Smartphones are 1608.85 and 1409.164 respectively. We can see from the Variances row that they are not precisely equal, but they are close enough to be assumed to have equal variances. The most relevant metric is the p-value .

The difference between means is statistically significant if the p-value is less than your significance level. Excel calculates p-values for one- and two-tailed T Tests .

One-tailed T Tests can detect only one direction of difference between means. A one-tailed test, for example, might only evaluate whether Smartphones have higher prices than Laptops . Two-tailed tests can reveal differences that are larger or smaller than. There are some other disadvantages to utilizing one-tailed testing, so I’ll continue with the conventional two-tailed results.

For our results, we’ll utilize P(T=t) two-tail, which is the p-value for the t-test’s two-tailed version. We cannot reject the null hypothesis because our p-value ( 0.095639932 ) is greater than the conventional significance level of 0.05 . The hypothesis that the population means differ is supported by our sample data. The mean price of Laptops is greater than the mean price of Smartphones’ .

The Analysis ToolPak also returns results for a one-tailed t-test . Here, the one-tailed P value of the two-sample equal variance t-test is 1.734 .

Case 2.2 – Paired T-Test

Select the t-Test: Paired Two Samples for Mean when you open the Data Analysis window.

Showing T-Test Result for Paired Test

The result shows that the mean for the Workpace is 104 and the mean for the Efficiency is 96.56 .

The difference between means is statistically significant if the p-value is less than your significance level. For our results, we’ll utilize P(T=t) two-tail, which is the p-value for the t-test’s two-tailed version. We cannot reject the null hypothesis because our p-value ( 0.188 ) is greater than the conventional significance level of 0.05 . The hypothesis that the population means differ is supported by our sample data. In particular, the Workpace mean exceeds the Efficiency mean.

How to Interpret T-Test Results in Excel

Let’s bring out the results again.

Two Sample Equal Variance t-Test Interpretation

  • The mean of laptop prices = 1608.85
  • The mean of smartphone prices = 1409.164

ii. Variance

  • The variance of laptop prices = 77622.597
  • The variance of smartphone prices = 51313.7904

iii. Observations

The number of observations for both laptops and smartphones are 10 .

iv. Pooled Variance

The samples’ average variance, calculated by pooling the variances of each sample.

The mathematical formula for this parameter is:

((No of observations of Sample 1-1)*(Variance of Sample 1) + (No of observations of Sample 2-1)*(Variance of Sample 2))/(No of observations of Sample 1 + No of observations of Sample 2 – 2)

So it becomes: ((10-1)*77622.59676+(10-1)*51313.7904)/(10+10-2) = 64468.19358

v. Hypothesized Mean Difference

We “hypothesize” that the number is the difference between the two population means. In this situation, we chose 0 because we want to see if the difference between the means of the two populations is zero.

It indicates the value of the Degrees of Freedom. Formula for this parameter is:

No of observations of Sample 1 + No of observations of Sample 2 – 2 = 10 + 10 – 2 = 18

vii. t-Stat

The test statistic value of the t-Test operation.

The formula for this parameter is given below.

(Mean of Sample 1 – Mean of Sample 2)/(Square root of (Pooling Variance* (1/No of observations of Sample 1 + 1/No of observations of Sample 2)))

So it becomes: (1608.85 – 1409.164)/Sqrt(64468.19358 * (1/10 + 1/10)) = 1.758570846

viii. P(T<=t) two-tail

A two-tailed t-test’s p-value. This value can be found by entering t = 1.758570846 with 18 degrees of freedom into any T Score to P Value Calculator.

In this situation, the value of p is 0.095639932 . Because this is greater than 0.05 , we cannot reject the null hypothesis. This suggests that we lack adequate evidence to conclude that the two population means differ.

ix. t-Critical two-tail

This is the test’s crucial value. A t-Critical value Calculator with 18 degrees of freedom and a 95% confidence level can be used to calculate this number.

In this instance, the critical value is 2.10092204 . We cannot reject the null hypothesis because our test statistic t is less than this number. Again, we lack adequate information to conclude that the two population means are distinct.

Things to Remember

  • Excel demands that your data be arranged in columns, with data from each group in a separate column. The first row should have labels or headers.
  • Clearly state your null hypothesis (usually that there is no significant difference between the group means) and your alternative hypothesis (the opposite of the null hypothesis).
  • As a result of the t-test, Excel returns the p-value. A small p-value (usually less than the specified alpha level) indicates that the null hypothesis may be rejected and that there is a substantial difference between the group means.

Frequently Asked Questions

Can I perform a t-test on unequal sample sizes in Excel?

Yes, you can use the T.TEST function to do a t-test on unequal sample sizes. When calculating the test statistic, Excel automatically accounts for unequal sample sizes.

What is the difference between a one-tailed and a two-tailed t-test?

A one-tailed t-test determines if the means of the two groups differ substantially in a given direction (e.g., greater or smaller). A two-tailed t-test looks for any significant difference, regardless of direction.

Can I calculate the effect size in Excel for t-tests?

While there is no built-in tool in Excel to calculate effect size, you can manually compute Cohen’s d for independent t-tests and paired sample correlations for paired t-tests using Excel’s basic mathematical operations.

<< Go Back to Excel for Statistics  |  Learn Excel

What is ExcelDemy?

Tags: Statistical Significance in Excel

Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

  • User Reviews
  • List of Services
  • Service Pricing

trustpilot review

  • Create Basic Excel Pivot Tables
  • Excel Formulas and Functions
  • Excel Charts and SmartArt Graphics
  • Advanced Excel Training
  • Data Analysis Excel for Beginners

DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

ExcelDemy

Excel Dashboards

Excel Tutorial: How To Test Hypothesis In Excel

Introduction.

Hypothesis testing is a crucial part of data analysis, helping us make informed decisions based on statistical evidence. It allows us to determine if there is enough evidence to support or reject a claim about a population parameter. In this Excel tutorial, we will delve into the process of testing hypothesis in Excel , providing you with the knowledge and skills to confidently analyze and draw conclusions from your data.

So why is hypothesis testing so important? Well, it helps us make sense of the overwhelming amount of data we encounter in our professional and personal lives. Whether we are trying to understand consumer behavior, assess the effectiveness of a new product, or evaluate the impact of a marketing campaign, hypothesis testing allows us to make informed decisions and draw reliable conclusions.

Key Takeaways

  • Hypothesis testing is essential for making informed decisions based on statistical evidence.
  • Understanding null and alternative hypotheses, as well as type I and type II errors, is crucial in hypothesis testing.
  • Setting up and organizing data accurately in Excel is necessary for conducting hypothesis tests.
  • Interpreting the results of a hypothesis test, including determining the p-value and significance level, is important for drawing reliable conclusions.
  • Avoiding common mistakes such as misinterpreting results and using the wrong test for the data is vital in hypothesis testing.

Understanding hypothesis testing

Hypothesis testing is a crucial concept in statistics that allows us to make inferences about a population based on a sample. In the context of Excel, understanding hypothesis testing is essential for data analysis and decision-making.

In hypothesis testing, the null hypothesis ( H0 ) is a statement that there is no effect or no difference in the population parameter. It is typically the hypothesis that researchers aim to refute. On the other hand, the alternative hypothesis ( Ha ) is a statement that there is an effect or a difference in the population parameter. It represents what the researchers are trying to prove.

In hypothesis testing, there are two types of errors that can occur. A Type I error occurs when the null hypothesis is rejected when it is actually true. This is also known as a false positive. A Type II error occurs when the null hypothesis is not rejected when it is actually false. This is also known as a false negative.

The significance level, often denoted as α , is the probability of rejecting the null hypothesis when it is true. In hypothesis testing, choosing the appropriate significance level is crucial as it determines the likelihood of making a Type I error. Commonly used significance levels include 0.05, 0.01, and 0.10.

Setting up the data in Excel

When conducting hypothesis testing in Excel, it is crucial to properly set up your data to ensure accurate results. Here are the key steps to follow:

The first step in testing a hypothesis in Excel is to input your data into the spreadsheet. This may include numerical values, categorical data, or any other relevant information for your analysis.

Once the data is inputted, it is important to organize it in a way that is conducive to hypothesis testing. This may involve structuring the data into relevant columns and rows, or creating separate sheets for different variables.

Prior to conducting hypothesis testing, it is essential to ensure that the data is accurate and complete. This may involve checking for any missing or erroneous values, as well as verifying the overall integrity of the dataset.

  • Input all relevant data into the Excel spreadsheet.
  • Organize the data in a manner that facilitates hypothesis testing.
  • Verify the accuracy and completeness of the data before proceeding with hypothesis testing.

Performing a hypothesis test in Excel

When it comes to testing hypotheses in Excel, there are a few key steps to follow to ensure accurate and reliable results. Here, we'll delve into the process of performing a hypothesis test in Excel, covering everything from selecting the appropriate test for the data to interpreting the test results.

Before diving into the hypothesis testing process, it's crucial to determine the appropriate test for the data at hand. This involves understanding the nature of the data and the specific hypothesis being tested. Whether it's a t-test, chi-squared test, ANOVA, or another statistical test, choosing the right test is essential for obtaining meaningful results.

Excel offers a range of built-in functions that make hypothesis testing relatively straightforward. Functions like T.TEST, CHISQ.TEST, and ANOVA help streamline the process, allowing users to input their data and quickly obtain test statistics and p-values. Understanding how to utilize these functions is key to executing hypothesis tests accurately.

Once the hypothesis test has been run in Excel, it's important to carefully interpret the results. This involves analyzing the test statistic, p-value, and any relevant confidence intervals to determine whether the data provides enough evidence to support or reject the null hypothesis. Excel's output can provide valuable insights into the significance of the findings, helping to draw meaningful conclusions from the hypothesis test.

Interpreting the results

After conducting a hypothesis test in Excel, it is important to carefully interpret the results to draw meaningful conclusions.

Understanding the p-value

The p-value is a crucial indicator of the strength of evidence against the null hypothesis. A low p-value (typically less than 0.05) suggests that the results are statistically significant, and the null hypothesis can be rejected in favor of the alternative hypothesis.

Significance level

The significance level, often denoted as alpha (α), is the threshold at which the p-value is considered significant. Commonly used significance levels include 0.05 and 0.01.

Rejecting or failing to reject the null hypothesis

Based on the obtained p-value and significance level, it is possible to determine whether the null hypothesis should be rejected or retained. If the p-value is less than the significance level, the null hypothesis is typically rejected in favor of the alternative hypothesis.

Considering the practical significance

In addition to statistical significance, it is important to consider the practical implications of the results. Even if a hypothesis is statistically significant, it may not have meaningful real-world impact.

Interpreting the findings in context

It is essential to discuss the implications of the hypothesis test within the specific context of the research or analysis. This involves considering the broader implications and potential applications of the results.

Considering limitations and alternative explanations

Discussing the potential limitations of the hypothesis test and considering alternative explanations for the results can provide a more comprehensive understanding of the findings.

Common mistakes to avoid

When conducting hypothesis testing in Excel, it's important to be aware of common mistakes that can lead to inaccurate results. Here are some key pitfalls to watch out for:

Misinterpreting the results of hypothesis tests is a common mistake that can lead to faulty conclusions. It's important to thoroughly understand the output of the test and consider the implications of the results before drawing any conclusions.

Using the wrong hypothesis test for the type of data being analyzed can lead to incorrect results. It's essential to select the appropriate test based on the nature of the data and the research question being addressed.

Failing to check for data integrity before conducting hypothesis tests can result in unreliable results. It's crucial to ensure that the data being analyzed is accurate and free from errors or anomalies that could impact the validity of the test.

Recap: Hypothesis testing is a crucial step in data analysis as it allows us to make informed decisions based on the evidence provided by the data.

Encouragement: I highly encourage you to apply the tutorial on hypothesis testing in Excel to your own data analysis projects. It's a valuable skill that can greatly enhance the quality and reliability of your conclusions.

Final Thoughts: The significance of hypothesis testing in Excel cannot be understated. It is a powerful tool that enables us to make conclusions about the population based on sample data, ultimately leading to more accurate and meaningful insights.

Excel Dashboard

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

Mastering Excel Dashboards for Data Analysts

The Benefits of Excel Dashboards for Data Analysts

Exploring the Power of Real-Time Data Visualization with Excel Dashboards

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

How to Connect Your Excel Dashboard to Other Platforms for More Focused Insights

Unlocking the Potential of Excel's Data Dashboard

10 Keys to Designing a Dashboard with Maximum Impact in Excel

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Essential Features for Data Exploration in Excel Dashboards

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Real-Time Dashboard Updates in Excel

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Interpreting Excel Dashboards: From Data to Action

Unleashing the Power of Excel Dashboards

Different Approaches to Excel Dashboard Design and Development

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Best Excel Dashboard Tips for Smarter Data Visualization

Leverage Your Data with Excel Dashboards

How to Create Effective Dashboards in Microsoft Excel

Crafting the Perfect Dashboard for Excel

Dashboards in Excel: Managing Data Analysis and Visualization

An Introduction to Excel Dashboards

Best Practices for Designing an Insightful Excel Dashboard

How to Create an Effective Excel Dashboard

  • Choosing a selection results in a full page refresh.

Hypothesis Test in Excel for the Population Mean (Large Sample)

Microsoft Excel for statistics > Hypothesis Test in Excel #1

Note : This article covers z-tests in Excel. If you have a small sample (under 30), or don’t know the population standard deviation , run a T Test in Excel instead.

Hypothesis Test in Excel: Overview

Hypothesis Test in Excel

Hypothesis Test in Excel: Two Sample for Means

Hypothesis test in excel: manual steps.

Step 1: Type your data into a single column in Excel. For example, type your data into cells A1:A40.

Step 2: Click the “Data” tab and then click “Data Analysis.” If you don’t see the Data Analysis button then you may need to load the Data Analysis Toolpak .

Step 3: Click “ Descriptive Statistics “ and then click “OK.” When the Descriptive Statistics dialog box opens, click “Summary Statistics” and then type the location for a cell where you want your result to appear. For example, type”B1.”

Step 4: Click “OK. ” A variety of descriptive statistics, like the median and mode , will appear starting in cell B1.

Step 5: Find the cells that have the mean and the standard error results in it. If you typed in cell B1 in Step 3, your mean will be in cell C3 and your standard error will be in cell C4. Take a note of those cell locations.

Step 6: Type the following formula into cell D1 (assuming your mean is in cell C3 and your SE is in cell C4 — if they are not, you’ll need to adjust the formula): (C3-0)/C4

Change the “zero” to reflect the mean in your null hypothesis . For example, if your null hypothesis states that the mean is $7 per hour, then change the 0 to “7.”

Step 7: Press “Enter” to get the value of the test statistic. Compare the value to the accepted value for your mean from the z-table*. If the test statistic falls into the accepted range, then you will fail to reject the null hypothesis .

Subscribe to our YouTube channel for more Microsoft Excel for Statistics help and tips!

Comments are closed.

#1 Excel tutorial on the net

This example teaches you how to perform a t-Test in Excel . The t-Test is used to test the null hypothesis that the means of two populations are equal.

Below you can find the study hours of 6 female students and 5 male students.

t-Test in Excel

To perform a t-Test, execute the following steps.

1. First, perform an F-Test to determine if the variances of the two populations are equal. This is not the case.

2. On the Data tab, in the Analysis group, click Data Analysis.

Click Data Analysis

Note: can't find the Data Analysis button? Click here to load the Analysis ToolPak add-in .

3. Select t-Test: Two-Sample Assuming Unequal Variances and click OK.

Select t-Test: Two-Sample Assuming Unequal Variances

4. Click in the Variable 1 Range box and select the range A2:A7.

5. Click in the Variable 2 Range box and select the range B2:B6.

6. Click in the Hypothesized Mean Difference box and type 0 (H 0 : μ 1 - μ 2 = 0).

7. Click in the Output Range box and select cell E1.

t-Test Parameters

8. Click OK.

t-Test Result in Excel

Conclusion: We do a two-tail test (inequality). lf t Stat < -t Critical two-tail or t Stat > t Critical two-tail, we reject the null hypothesis. This is not the case, -2.365 < 1.473 < 2.365. Therefore, we do not reject the null hypothesis. The observed difference between the sample means (33 - 24.8) is not convincing enough to say that the average number of study hours between female and male students differ significantly.

  • Analysis ToolPak

Learn more, it's easy

  • Descriptive Statistics
  • Moving Average
  • Exponential Smoothing
  • Correlation

Download Excel File

  • t-test.xlsx

Next Chapter

  • Create a Macro

Follow Excel Easy

Excel Easy on Facebook

Become an Excel Pro

  • 300 Examples

t-Test • © 2010-2024 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA

Online Tutorials Library List | Tutoraspire.com

The Complete Guide: Hypothesis Testing in Excel

In statistics, a hypothesis test is used to test some assumption about a population parameter .

There are many different types of hypothesis tests you can perform depending on the type of data you’re working with and the goal of your analysis.

This tutorial explains how to perform the following types of hypothesis tests in Excel:

  • One sample t-test
  • Two sample t-test
  • Paired samples t-test
  • One proportion z-test
  • Two proportion z-test

Let’s jump in!

Example 1: One Sample t-test in Excel

A one sample t-test is used to test whether or not the mean of a population is equal to some value.

For example, suppose a botanist wants to know if the mean height of a certain species of plant is equal to 15 inches.

To test this, she collects a random sample of 12 plants and records each of their heights in inches.

She would write the hypotheses for this particular one sample t-test as follows:

  • H 0 :  µ = 15
  • H A :  µ ≠15

Refer to this tutorial for a step-by-step explanation of how to perform this hypothesis test in Excel.

Example 2: Two Sample t-test in Excel

A two sample t-test is used to test whether or not the means of two populations are equal.

For example, suppose researchers want to know whether or not two different species of plants have the same mean height.

To test this, they collect a random sample of 20 plants from each species and measure their heights.

The researchers would write the hypotheses for this particular two sample t-test as follows:

  • H 0 :  µ 1 = µ 2
  • H A :  µ 1 ≠ µ 2

Example 3: Paired Samples t-test in Excel

A paired samples t-test is used to compare the means of two samples when each observation in one sample can be paired with an observation in the other sample.

For example, suppose we want to know whether a certain study program significantly impacts student performance on a particular exam.

To test this, we have 20 students in a class take a pre-test. Then, we have each of the students participate in the study program for two weeks. Then, the students retake a post-test of similar difficulty.

We would write the hypotheses for this particular two sample t-test as follows:

  • H 0 :  µ pre = µ post
  • H A :  µ pre ≠ µ post

Example 4: One Proportion z-test in Excel

A  one proportion z-test  is used to compare an observed proportion to a theoretical one.

For example, suppose a phone company claims that 90% of its customers are satisfied with their service.

To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service.

  • H 0 : p = 0.90
  • H A : p ≠ 0.90

Example 5: Two Proportion z-test in Excel

A two proportion z-test is used to test for a difference between two population proportions.

For example, suppose a s uperintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences.

  • H 0 : p 1 = p 2
  • H A : p 1  ≠ p 2

How to Change Axis Scales in Google Sheets Plots

Statistics vs. analytics: what’s the difference, you may also like, how to create a stem-and-leaf plot in spss, how to create a correlation matrix in spss, excel: how to use if function with negative numbers, excel: how to use if function with text values, excel: how to use greater than or equal to in if function, excel: how to use if function with multiple conditions.

  • Mastering Hypothesis Testing in Excel: A Practical Guide for Students

Excel for Hypothesis Testing: A Practical Approach for Students

Angela O'Brien

Hypothesis testing lies at the heart of statistical inference, serving as a cornerstone for drawing meaningful conclusions from data. It's a methodical process used to evaluate assumptions about a population parameter, typically based on sample data. The fundamental idea behind hypothesis testing is to assess whether observed differences or relationships in the sample are statistically significant enough to warrant generalizations to the larger population. This process involves formulating null and alternative hypotheses, selecting an appropriate statistical test, collecting sample data, and interpreting the results to make informed decisions. In the realm of statistical software, SAS stands out as a robust and widely used tool for data analysis in various fields such as academia, industry, and research. Its extensive capabilities make it particularly favored for complex analyses, large datasets, and advanced modeling techniques. However, despite its versatility and power, SAS can have a steep learning curve, especially for students who are just beginning their journey into statistics. The intricacies of programming syntax, data manipulation, and interpreting output may pose challenges for novice users, potentially hindering their understanding of statistical concepts like hypothesis testing. Understanding hypothesis testing is essential for performing statistical analyses and drawing meaningful conclusions from data using Excel 's built-in functions and tools.

Excel for Hypothesis Testing

Enter Excel, a ubiquitous spreadsheet software that most students are already familiar with to some extent. While Excel may not offer the same level of sophistication as SAS in terms of advanced statistical procedures, it remains a valuable tool, particularly for introductory and intermediate-level analyses. Its intuitive interface, user-friendly features, and widespread accessibility make it an attractive option for students seeking a practical approach to learning statistics. By leveraging Excel's built-in functions, data visualization tools, and straightforward formulas, students can gain hands-on experience with hypothesis testing in a familiar environment. In this blog post, we aim to bridge the gap between theoretical concepts and practical application by demonstrating how Excel can serve as a valuable companion for students tackling hypothesis testing problems, including those typically encountered in SAS assignments. We will focus on demystifying the process of hypothesis testing, breaking it down into manageable steps, and showcasing Excel's capabilities for conducting various tests commonly encountered in introductory statistics courses.

Understanding the Basics

Hypothesis testing is a fundamental concept in statistics that allows researchers to draw conclusions about a population based on sample data. At its core, hypothesis testing involves making a decision about whether a statement regarding a population parameter is likely to be true. This decision is based on the analysis of sample data and is guided by two competing hypotheses: the null hypothesis (H0) and the alternative hypothesis (Ha). The null hypothesis represents the status quo or the absence of an effect. It suggests that any observed differences or relationships in the sample data are due to random variation or chance. On the other hand, the alternative hypothesis contradicts the null hypothesis and suggests the presence of an effect or difference in the population. It reflects the researcher's belief or the hypothesis they aim to support with their analysis.

Formulating Hypotheses

In Excel, students can easily formulate hypotheses using simple formulas and logical operators. For instance, suppose a researcher wants to test whether the mean of a sample is equal to a specified value. They can use the AVERAGE function in Excel to calculate the sample mean and then compare it to the specified value using logical operators like "=" for equality. If the calculated mean is equal to the specified value, it supports the null hypothesis; otherwise, it supports the alternative hypothesis.

Excel's flexibility allows students to customize their hypotheses based on the specific parameters they are testing. Whether it's comparing means, proportions, variances, or other population parameters, Excel provides a user-friendly interface for formulating hypotheses and conducting statistical analysis.

Selecting the Appropriate Test

Excel offers a plethora of functions and tools for conducting various types of hypothesis tests, including t-tests, z-tests, chi-square tests, and ANOVA (analysis of variance). However, selecting the appropriate test requires careful consideration of the assumptions and conditions associated with each test. Students should familiarize themselves with the assumptions underlying each hypothesis test and assess whether their data meets those assumptions. For example, t-tests assume that the data follow a normal distribution, while chi-square tests require categorical data and independence between observations.

Furthermore, students should consider the nature of their research question and the type of data they are analyzing. Are they comparing means of two independent groups or assessing the association between categorical variables? By understanding the characteristics of their data and the requirements of each test, students can confidently choose the appropriate hypothesis test in Excel.

T-tests are statistical tests commonly used to compare the means of two independent samples or to compare the mean of a single sample to a known value. These tests are valuable in various fields, including psychology, biology, economics, and more. In Excel, students can employ the T.TEST function to conduct t-tests, providing them with a practical and accessible way to analyze their data and draw conclusions about population parameters based on sample statistics.

Independent Samples T-Test

The independent samples t-test, also known as the unpaired t-test, is utilized when comparing the means of two independent groups. This test is often employed in experimental and observational studies to assess whether there is a significant difference between the means of the two groups. In Excel, students can easily organize their data into separate columns representing the two groups, calculate the sample means and standard deviations for each group, and then use the T.TEST function to obtain the p-value. The p-value obtained from the T.TEST function represents the probability of observing the sample data if the null hypothesis, which typically states that there is no difference between the means of the two groups, is true.

A small p-value (typically less than the chosen significance level, commonly 0.05) indicates that there is sufficient evidence to reject the null hypothesis in favor of the alternative hypothesis, suggesting a significant difference between the group means. By conducting an independent samples t-test in Excel, students can not only assess the significance of differences between two groups but also gain valuable experience in data analysis and hypothesis testing, which are essential skills in various academic and professional settings.

Paired Samples T-Test

The paired samples t-test, also known as the dependent t-test or matched pairs t-test, is employed when comparing the means of two related groups. This test is often used in studies where participants are measured before and after an intervention or when each observation in one group is matched or paired with a specific observation in the other group. Examples include comparing pre-test and post-test scores, analyzing the performance of individuals under different conditions, and assessing the effectiveness of a treatment or intervention. In Excel, students can perform a paired samples t-test by first calculating the differences between paired observations (e.g., subtracting the before-measurement from the after-measurement). Next, they can use the one-sample t-test function, specifying the calculated differences as the sample data. This approach allows students to determine whether the mean difference between paired observations is statistically significant, indicating whether there is a meaningful change or effect between the two related groups.

Interpreting the results of a paired samples t-test involves assessing the obtained p-value in relation to the chosen significance level. A small p-value suggests that there is sufficient evidence to reject the null hypothesis, indicating a significant difference between the paired observations. This information can help students draw meaningful conclusions from their data and make informed decisions based on statistical evidence. By conducting paired samples t-tests in Excel, students can not only analyze the relationship between related groups but also develop critical thinking skills and gain practical experience in hypothesis testing, which are valuable assets in both academic and professional contexts. Additionally, mastering the application of statistical tests in Excel can enhance students' data analysis skills and prepare them for future research endeavors and real-world challenges.

Chi-Square Test

The chi-square test is a versatile statistical tool used to assess the association between two categorical variables. In essence, it helps determine whether the observed frequencies in a dataset significantly deviate from what would be expected under certain assumptions. Excel provides a straightforward means to perform chi-square tests using the CHISQ.TEST function, which calculates the probability associated with the chi-square statistic.

Goodness-of-Fit Test

One application of the chi-square test is the goodness-of-fit test, which evaluates how well the observed frequencies in a single categorical variable align with the expected frequencies dictated by a theoretical distribution. This test is particularly useful when researchers wish to ascertain whether their data conforms to a specific probability distribution. In Excel, students can organize their data into a frequency table, listing the categories of the variable of interest along with their corresponding observed frequencies. They can then specify the expected frequencies based on the theoretical distribution they are testing against. For example, if analyzing the outcomes of a six-sided die roll, where each face is expected to occur with equal probability, the expected frequency for each category would be the total number of observations divided by six.

Once the observed and expected frequencies are determined, students can employ the CHISQ.TEST function in Excel to calculate the chi-square statistic and its associated p-value. The p-value represents the probability of obtaining a chi-square statistic as extreme or more extreme than the observed value under the assumption that the null hypothesis is true (i.e., the observed frequencies match the expected frequencies). Interpreting the results of the goodness-of-fit test involves comparing the calculated p-value to a predetermined significance level (commonly denoted as α). If the p-value is less than α (e.g., α = 0.05), there is sufficient evidence to reject the null hypothesis, indicating that the observed frequencies significantly differ from the expected frequencies specified by the theoretical distribution. Conversely, if the p-value is greater than α, there is insufficient evidence to reject the null hypothesis, suggesting that the observed frequencies align well with the expected frequencies.

Test of Independence

Another important application of the chi-square test in Excel is the test of independence, which evaluates whether there is a significant association between two categorical variables in a contingency table. This test is employed when researchers seek to determine whether the occurrence of one variable is related to the occurrence of another. To conduct a test of independence in Excel, students first create a contingency table that cross-tabulates the two categorical variables of interest. Each cell in the table represents the frequency of occurrences for a specific combination of categories from the two variables.

Similar to the goodness-of-fit test, students then calculate the expected frequencies for each cell under the assumption of independence between the variables. Using the CHISQ.TEST function in Excel, students can calculate the chi-square statistic and its associated p-value based on the observed and expected frequencies in the contingency table. The interpretation of the test results follows a similar procedure to that of the goodness-of-fit test, with the p-value indicating whether there is sufficient evidence to reject the null hypothesis of independence between the two variables.

Excel, despite being commonly associated with spreadsheet tasks, offers a plethora of features that make it a versatile and powerful tool for statistical analysis, especially for students diving into the intricacies of hypothesis testing. Its widespread availability and user-friendly interface make it accessible to students at various levels of statistical proficiency. However, the true value of Excel lies not just in its accessibility but also in its ability to facilitate a hands-on learning experience that reinforces theoretical concepts.

At the core of utilizing Excel for hypothesis testing is a solid understanding of the fundamental principles of statistical inference. Students need to grasp concepts such as the null and alternative hypotheses, significance levels, p-values, and test statistics. Excel provides a practical platform for students to apply these concepts in a real-world context. Through hands-on experimentation with sample datasets, students can observe how changes in data inputs and statistical parameters affect the outcome of hypothesis tests, thus deepening their understanding of statistical theory.

Post a comment...

Mastering hypothesis testing in excel: a practical guide for students submit your homework, attached files.

File Actions

How to Do Hypothesis Tests With the Z.TEST Function in Excel

  • Statistics Tutorials
  • Probability & Games
  • Descriptive Statistics
  • Inferential Statistics
  • Applications Of Statistics
  • Math Tutorials
  • Pre Algebra & Algebra
  • Exponential Decay
  • Worksheets By Grade
  • Ph.D., Mathematics, Purdue University
  • M.S., Mathematics, Purdue University
  • B.A., Mathematics, Physics, and Chemistry, Anderson University

Hypothesis tests are one of the major topics in the area of inferential statistics. There are multiple steps to conduct a hypothesis test and many of these require statistical calculations. Statistical software, such as Excel, can be used to perform hypothesis tests. We will see how the Excel function Z.TEST tests hypotheses about an unknown population mean.

Conditions and Assumptions

We begin by stating the assumptions and conditions for this type of hypothesis test. For inference about the mean we must have the following simple conditions:

  • The sample is a simple random sample .
  • The sample is small in size relative to the population . Typically this means that the population size is more than 20 times the size of the sample.
  • The variable being studied is normally distributed.
  • The population standard deviation is known.
  • The population mean is unknown.

All of these conditions are unlikely to be met in practice. However, these simple conditions and the corresponding hypothesis test are sometimes encountered early in a statistics class. After learning the process of a hypothesis test, these conditions are relaxed in order to work in a more realistic setting.

Structure of the Hypothesis Test

The particular hypothesis test we consider has the following form:

  • State the null and alternative hypotheses .
  • Calculate the test statistic, which is a z -score.
  • Calculate the p-value by using the normal distribution. In this case the p-value is the probability of obtaining at least as extreme as the observed test statistic, assuming the null hypothesis is true.
  • Compare the p-value with the level of significance to determine whether to reject or fail to reject the null hypothesis.

We see that steps two and three are computationally intensive compared two steps one and four. The Z.TEST function will perform these calculations for us.

Z.TEST Function

The Z.TEST function does all of the calculations from steps two and three above. It does a majority of the number crunching for our test and returns a p-value. There are three arguments to enter into the function, each of which is separated by a comma. The following explains the three types of arguments for this function.

  • The first argument for this function is an array of sample data. We must enter a range of cells that corresponds to the location of the sample data in our spreadsheet.
  • The second argument is the value of μ that we are testing in our hypotheses. So if our null hypothesis is H 0 : μ = 5, then we would enter a 5 for the second argument.
  • The third argument is the value of the known population standard deviation. Excel treats this as an optional argument

Best Excel Tutorial

The largest Excel knowledge base ✅ The best place to learn Excel online ❤️

partial countif data table

How to Test Hypothesis in Excel

In this Excel tutorial, you will learn how to test hypothesis in Excel application based on given data arrays. This is a testing that make it possible to test if two range are equal to one another.

Table of Contents

Hypothesis t-Test Testing using T.Test Excel function

To test t test hypotesis in Excel you can just use T.Test function.

The syntax of T.TEST Excel function:

  • Array1: the first set of data to test
  • Array2: the second set of data to test
  • Tails: the number of tails where 1. is one-tailed distribution and 2 is for two tailed distribution
  • Type: 1 is for paired, 2 for homoscedastic, 3 for heteroscedastic

This is the data set and two arrays for my t hypothesis. I’d like to test the hypothesis if the is a difference in given arrays.

hypothesis testing data table

The formula I used for ttest hypothesis is  =T.TEST($B$2:$B$6,$C$2:$C$8,1,3) because the variance  of these arrays is different.

hypothesis testing ttest formula

Interpret the results of your hypothesis test in the context of your research question. Explain what the results mean and how they support or refute your hypothesis. To do that you need to interpret the calculated probability . Calculated p ≥ 0.05 means that difference is not significant and p ≤ 0.05 means that difference is significant. The hypotesis result is 0.406325 so the difference is not significant.

Hypothesized difference has been calculated but let’s check one more method with an add-in.

Hypothesis t-Test Testing using Analysis Toolpak Add-in

There is also a possibility to perform the same t hypothesis testing using Analysis Toolpak Add-in.

Click on Data on the top, beside formula. Click Data analysis.

hypothesis testing ribbon data analysis

Note: The data analysis is quite standard. But if it does not show under the data, then it is more likely that it has not been added, which could be done by clicking on File > Options > Add-Ins > Clicking Go on the down side when manage shows Excel Add-ins, and then choosing Data Analysis, and it will be ready.

Browse the Data Analysis, and choose the t-text: two-sample assuming unequal variances.

hypothesis testing ttext two-sample assuming unequal variances

Select the data for the two columns, write 0 in the Hypothesized mean difference, select the cell desired in the output range.

excel hypothesis test

And this is how to handle Hypothesis Testing in Excel.

Related posts:

Correlation data

Your email address will not be published. Required fields are marked *

  • 800 -274-2874
  • [email protected]

Hypothesis Testing

hypothesis

So, a hypothesis is just a statement of theory.  It may or may not be true.  A drug company can claim that a new drug is better at decreasing blood pressure.   You may claim that the diet plan you created helps people lose more weight than a nationally known diet plan.  All these things are just statements – just hypotheses.

The hypothesis is the starting point.  From there, we have to test the hypothesis and reach a decision if the hypothesis is probably true or probably false.  Note the word “probably.”  There is always variation – so there is always a chance for you to make the wrong decision.  This month’s publication takes a look at the five steps involved in conducting a hypothesis test.

In this issue:

  • The problem
  • A brief pause for the standard normal distribution
  • Formulate the null hypothesis and the alternative hypothesis
  • Determine the significance level
  • Collect the data and calculate the sample statistics
  • Calculate the p value for the hypothesis test
  • Compare the p value to the desired significance level

Quick Links

You can download this publication as a pdf here .

The Problem

six sigma

The average coating thickness is 5 mil.  You want to be sure that the coating thickness remains the same before you will approve the process change.

The team wants to perform a hypothesis test to prove that the average coating thickness will not change.  The team will go through the basic five steps of hypothesis testing:

The details of the five steps are shown below.  However, before those steps are covered, a review of the standard normal distribution is needed.  This will be required when we do some calculations.

A Brief Pause for the Standard Normal Distribution

We need to digress a moment here because we will need to make use of a special case of the normal distribution – when the average = 0 and the standard deviation = 1. This special case is called the standard normal distribution and is shown in Figure 1.

Figure 1: Standard Normal Distribution

standard_normal_curve

For this distribution, the area under the curve from -∞ to +∞ is equal to 1.0. In addition, the area under the curve is proportional to the fraction of measurements that fall in that region. These two facts can used to help determine the fraction of measurements that fall above some value (such as a specification limit), below some value, or between two values.

histogram

z=  (x- μ)/σ

where x is some value, μ is the average, and σ is the standard deviation of the x values.  The value of z (the z score) is simply how many standard deviations a value, x, is from the average.

For example, suppose x is 1.5 standard deviations below the average.  In this case, z = -1.5.  The area below z = -1.5 is the percentage of x values that are more than 1.5 standard deviations below the average.  For z = -1.5, that area is 6.68% as is shown in Figure 1.   If z = 1.5, then the area above z = 1.5 is the percentage of x values that are more than 1.5 standard deviations above the average.  This area is also 6.68%.

To find the percentage of data within z = -1.5 and z = 1.5, you simply use the fact that the area under the curve is 100%, so the percentage of data between the two z values is 100 – 6.68 – 6.68 = 86.64%.  You can determine these percentages from a table of z values (see our publication on the normal distribution ) or by using Excel’s NORMSDIST function.

These percentages can also be viewed as probabilities, e.g., the probability of getting a result that is less than -1.5 standard deviations below the average is 0.0668.  We will make use of this knowledge below.  Now back to the steps in hypothesis testing.

Step 1: Formulate the Null Hypothesis and Alternative Hypothesis

Hypothesis testing

So the null hypothesis (H 0 ) is that the process change will not impact the average coating thickness; the average coating thickness (μ) will remain at 5.  This is usually written as:

Now for the alternative hypothesis, which is denoted by H 1 .  The alternative hypothesis is that the process change will have an effect on the average coating thickness and the average coating thickness will not equal 5.  This is usually written as:

This is called a two-sided hypothesis test since you are only interested if the mean is not equal to 5.  You can have one-sided tests where you want the mean to be greater than or less than some value.

Step 2: Determine the Significance Level You Want

The significance level is important in hypothesis testing.  It is the probability of rejecting the null hypothesis when it is true.  This probability is denoted by α.  Typical values of  α include 0.05 and 0.01.  You decide that you want α to be 0.05.  This means that there is only a 5% of chance of rejecting the null hypothesis when it is actually true.

Step 3: Collect the Data and Calculate the Sample Statistics

data

X   = average coating thickness = 5.06

s = standard deviation of the coating thickness = 0.20

We have our statistics.  How do you decide to accept or reject the null hypothesis?  The way you do this is to assume that the null hypothesis is true and then determine the probability (p value) of getting this sample average.  If the p value is large, it means that there is large probability of getting an average thickness of 5.06 with a standard deviation of 0.20 when the null hypothesis is true and you will accept that the null hypothesis is probably true.  But if the probability of getting these statistics is small, you will assume that the null hypothesis is probably not true and reject it in favor the alternative hypothesis.

Step 4: Calculate the p Value

To determine this probability, you will need to consider your sampling distribution.    The distribution of sample averages tends to be normal when the sample size is large enough.  We will use this assumption here.  So, your sampling distribution is represented by all the possible sample averages of sample size 25 from the population of coating thicknesses.  This normal distribution is shown in Figure 2.

Figure 2: Normal Distribution for Sample Averages

sampling distribution

The highest point on the curve is the average.  The population average of the sample averages (μ X ) is equal to the population average, μ, so we have just used μ in Figure 1.  The standard deviation of the sample averages is denoted by σ X .

To be able to draw your sampling distribution, you need to know μ X   and  σ X .  Since you assumed that the null hypothesis is true, μ X   = 5.0.  The standard deviation of the sample averages is given by:

where σ is the population standard deviation and n is the sample size.

You don’t know what the population standard deviation is, but you have an estimate from the sample statistics.  The standard deviation of the 25 samples was 0.2.  You can use this as the population standard deviation.

σ X =σ/√n =  s/√n=0.2/√25=0.04

Now you can draw the sampling distribution and add the sample average as shown in Figure 3.

Figure 3: Sampling Distribution

sampling distrbution with mean = 5

Now we return to the z score.  Remember, the z score is a measure of how many standard deviations the sample average ( X  )is from the population average (μ).   For this example, the z value is calculated as:

z=  ( X -μ)/σ X =(5.06-5)/.04=.06/.04=1.5

So, 5.06 is 1.5 standard deviations away from the average.    As shown above, the probability of getting a result that is 1.5 standard deviations away from the average is 0.0668.  Remember, this a two-side test, so you didn’t care if the difference was above or below the average.  So, the probability of getting an average that is more than 1.5 standard deviations away from the average is 2(0.0668) = 0.1336 or 13.36%.  This is the p value:

p value = 0.1336

Remember what the p value represents.  You assumed that the null hypothesis is true.  The p value is the probability of getting this result (or a more extreme result) if the null hypothesis is true.

Step 5:  Compare the p value to the Desired Significance Level

In step 2, we set the significance level at 0.05.  Since our p value is greater than this, we conclude that the coating thickness was not impacted by the process change.  We accept the null hypothesis as probably being true.  If the p value had been less than 0.05, we would rejected the null hypothesis and said that the process change did impact the coating thickness.

This newsletter has taken a look at how to perform hypothesis testing.  The five steps are:

  • Determine the significance level you want

The normal distribution was used to demonstrate how hypothesis testing is done.  You will not always be dealing with the normal distribution but the process is essentially the same.  One item that is still to be discussed is how to select the sample size.  This will be the subject of a later publication.

  • SPC for Excel Software
  • Visit our home page
  • SPC Training
  • SPC Consulting
  • Ordering Information

Thanks so much for reading our SPC Knowledge Base. We hope you find it informative and useful. Happy charting and may the data always support your position.

Dr. Bill McNeese BPI Consulting, LLC

View Bill McNeese

Connect with Us

  • Basic Statistics
  • Item Analysis
  • Analysis of Individual Values (ANOX)
  • Nonparametric Techniques for Comparing Processes
  • Nonparametric Techniques for a Single Sample
  • Descriptive Statistics
  • Interpretation of Alpha and p-Value
  • Just Because There is a Correlation, Doesn’t Mean ….
  • Deciding Which Distribution Fits Your Data Best
  • Distribution Fitting
  • Box-Cox Transformation
  • What? My Data are Not Normal?
  • Are Skewness and Statistics Useful Statistics – Revisited
  • How Many Samples Do I Need?
  • Anderson-Darling Test for Normality
  • Polls, Sample Size, and Error Margins
  • Normal Probability Plots
  • Normal Distribution
  • Are the Skewness and Kurtosis Useful Statistics?
  • Inspecting Supplier Material
  • Explaining Standard Deviation

guest

SPC Knowledgebase Newsletter and Videos

IMAGES

  1. Hypothesis Testing Formula

    hypothesis testing excel

  2. Hypothesis testing in excel

    hypothesis testing excel

  3. T Test Using Using Excel's Data Analysis Tool for Hypothesis Testing

    hypothesis testing excel

  4. Using Microsoft Excel for One Sample Hypothesis Test

    hypothesis testing excel

  5. Hypothesis Testing Population Proportion

    hypothesis testing excel

  6. Chapter 10 Excel Tutorial on Hypothesis Testing of Two Populations

    hypothesis testing excel

VIDEO

  1. Hypothesis Testing Excel Examples

  2. Calculating Independent Hypothesis Test Values in Excel

  3. Hypothesis Testing using EXCEL (Statistical Treatment for Action Research) EASY

  4. Hypothesis Testing One Sample T Test

  5. Microsoft Excel Hypothesis Testing for Proportion

  6. Tutorial Excel for research data analysis:Hypothesis testing ,Students t-test, practical approach

COMMENTS

  1. The Complete Guide: Hypothesis Testing in Excel

    To test this, they collect a random sample of 20 plants from each species and measure their heights. The researchers would write the hypotheses for this particular two sample t-test as follows: H0: µ1 = µ2. HA: µ1 ≠ µ2. Refer to this tutorial for a step-by-step explanation of how to perform this hypothesis test in Excel.

  2. How to do t-Tests in Excel

    Learn how to use Excel to perform and interpret t-tests, which are hypothesis tests that assess the means of one or two groups. Find out how to choose the correct form of t-test, install the Data Analysis ToolPak, and access additional resources.

  3. The Complete Guide: Hypothesis Testing in Excel

    Learn how to perform different types of hypothesis tests in Excel, such as t-tests and z-tests, with step-by-step explanations and examples. Find out how to test assumptions, compare means, proportions, and more.

  4. How to Do a T Test in Excel (2 Ways with Interpretation of Results)

    Click on the Data Analysis button from the ribbon of the Data tab. The Data Analysis features will appear. Select t-Test: Two Sample Assuming Equal Variances and click OK. Set up the parameters for the t-test operation. Insert the Laptop and Smartphone prices as Variable 1 Range and Variable 2 Range.

  5. Excel Tutorial: How To Test Hypothesis In Excel

    When conducting hypothesis testing in Excel, it is crucial to properly set up your data to ensure accurate results. Here are the key steps to follow: A. Inputting the data into the Excel spreadsheet. The first step in testing a hypothesis in Excel is to input your data into the spreadsheet. This may include numerical values, categorical data ...

  6. Hypothesis Test in Excel for the Population Mean (Large Sample)

    Hypothesis Test in Excel: Manual Steps. Step 1: Type your data into a single column in Excel. For example, type your data into cells A1:A40. Step 2: Click the "Data" tab and then click "Data Analysis.". If you don't see the Data Analysis button then you may need to load the Data Analysis Toolpak. Step 3: Click " Descriptive ...

  7. t-Test in Excel (In Easy Steps)

    This example teaches you how to perform a t-Test in Excel. The t-Test is used to test the null hypothesis that the means of two populations are equal. Below you can find the study hours of 6 female students and 5 male students. H 0: μ 1 - μ 2 = 0 H 1: μ 1 - μ 2 ≠ 0

  8. Hypothesis t-test for One Sample Mean using Excel's Data Analysis

    This video shows how to conduct a one-sample hypothesis t-test for the mean in Microsoft Excel using the built-in Data Analysis (from raw data).How to load ...

  9. Hypothesis test (t-test) for a mean in Excel

    Dr Nic shows how to use Excel to perform a hypothesis test for mean using Excel. She also shows the overall hypothesis testing process, linked in with her ot...

  10. The Complete Guide: Hypothesis Testing in Excel

    There are many different types of hypothesis tests you can perform depending on the type of data you're working with and the goal of your analysis. This tutorial explains how to perform the following types of hypothesis tests in Excel: One sample t-test. Two sample t-test. Paired samples t-test. One proportion z-test. Two proportion z-test.

  11. Excel for Hypothesis Testing: A Practical Approach for Students

    Understanding hypothesis testing is essential for performing statistical analyses and drawing meaningful conclusions from data using Excel 's built-in functions and tools. Enter Excel, a ubiquitous spreadsheet software that most students are already familiar with to some extent. While Excel may not offer the same level of sophistication as SAS ...

  12. How to Conduct a One Sample t-Test in Excel

    n = sample size. The following image shows how to calculate t in Excel: The test statistic t turns out to be -1.68485. Step 3: Calculate the p-value of the test statistic. Next, we need to calculate the p-value associated with the test statistic using the following function in Excel: =T.DIST.2T (ABS (x), deg_freedom)

  13. Complete Guide: How to Interpret t-test Results in Excel

    Step 2: Perform the Two Sample t-test. To perform a two sample t-test in Excel, click the Data tab along the top ribbon and then click Data Analysis: If you don't see this option to click on, you need to first download the Analysis ToolPak. In the window that appears, click the option titled t-Test: Two-Sample Assuming Equal Variances and ...

  14. How to Do Hypothesis Tests With the Z.TEST Function in Excel

    Learn how to perform hypothesis tests about an unknown population mean using the Z.TEST function in Excel. See the conditions, assumptions, structure and examples of this statistical test.

  15. Hypothesis Testing

    Hypothesis Testing. Central to statistical analysis is the notion of hypothesis testing. We now review hypothesis testing (via null and alternative hypotheses), as well as consider the related topics of confidence intervals, effect size, statistical power, and sample size requirements. Concepts introduced in this part of the website will seem ...

  16. One-Sample t-Test

    The following Real Statistics function fills in this gap. Real Statistics Function: The Real Statistics Resource Pack provides the following function: T1_TEST (R1, hyp, tails) = the p-value of the one-sample t-test for the data in array R1 based on the hypothetical mean hyp (default 0) where tails = 1 or 2 (default).

  17. PDF Hypothesis Testing with Excel

    Hypothesis Testing with Excel Robb T. Koether Introduction Testing the Significance of the Regression Line The Goodness of Fit Test The Test of Independence Test of Independence To perform the test of independence, we must calculate the expected counts outselves. Create the array of observed counts. Enter the observed counts in an array of ...

  18. Hypothesis Testing in Excel

    87 pages of complete step-by-step instructions showing how to perform every type of hypothesis test and how to do them all in Excel. This e-manual will make you an expert on knowing exactly how and when to use all types of hypothesis tests (hypothesis tests of mean vs. proportion, one-tailed vs. two-tailed tests, one-sample vs. two-sample tests, and unpaired data vs. paired data tests) and how ...

  19. How to Conduct a Two Sample t-Test in Excel

    If you don't see this as an option to click on, you need to first download the Analysis ToolPak, which is completely free. Step 3: Select the appropriate test to use. Select the option that says t-Test: Two-Sample Assuming Equal Variances and then click OK. Step 4: Enter the necessary info.

  20. How to Test Hypothesis in Excel

    Hypothesis t-Test Testing using T.Test Excel function. To test t test hypotesis in Excel you can just use T.Test function. The syntax of T.TEST Excel function: Array1: the first set of data to test; Array2: the second set of data to test; Tails: the number of tails where 1. is one-tailed distribution and 2 is for two tailed distribution

  21. 6.2 Hypothesis Testing

    6 Hypothesis Testing - One Population Mean, Proportion, and Dependent Populations ... Please view the video below to learn to perform a one-sample hypothesis test using Excel. 6.2 Hypothesis Testing - Single Population Mean using Excel is shared under a not declared license and was authored, ...

  22. Hypothesis Testing

    The team will go through the basic five steps of hypothesis testing: Formulate the null hypothesis and the alternative hypothesis. Determine the significance level. Collect the data and calculate the sample statistics. Calculate the p value for the hypothesis test. Compare the p value to the desired significance level.