How to Calculate t test Using Excel for Unrelated Groups (Independent groups)



in this tutorial I'm going to talk about T statistics between means of different groups using Excel typically this test is perform between an experiment group and a control group the final Excel spreadsheet looks like this and I'm going to walk you through it step by step I'm also going to discuss how to interpret the results of a t-test and also how to read the table like always we always set up a null hypothesis and our null hypothesis is our experiment group and control groups means or exactly the same let me draw a bell curve for you so I'll draw that in right there we establish all put in critical regions which are the red area rejection regions I should say and what we try to determine is the experiment group the mean of the experiment group and the mean of the control group are there means significantly different if the mean of the control group is Falls into the red area then we reject the null hypothesis I will conduct a t-test and come up with a number 40 and ultimately I will trigger on is the T in the red area and if it's in the red area we reject the null hypothesis if the result or the T score is in the gray area we fail to reject the null hypothesis I'm going to compare two groups I'm going to compare the means the number of participants and the standard deviation I'll do that for both groups I'll calculate this for both groups the actual formula is really nasty looking and what I do is I compare the means of both groups Group A and Group B divided by all this stuff and it's just that don't worry about the formula because I'll write it here it's the sample size minus one times the standard deviation of Group a squared plus the sample size of Group B minus one times the standard deviation squared which is also the variance by the way divided by the sample size of Group A plus the sample size of Group B minus two higher multiply this times the sample size of Group A plus Group B divided by n times n or the sample size of Group A times the sample size over B and I'm multiplying this I'll put it brackets around at times this other one with brackets around it I'm going to show you how to do this excel I really am but I want to get everything set up for you first so now I have I'm gonna put some numbers to this use this equation and I'm going to put in some numbers and let's say I have a hormone treatment with some numbers and a placebo which is my control group and I'm going to calculate the mean and the standard deviation and the number of participants and I'll do this in Excel so don't worry about the numbers and there's ten observations by the way and I'll do this for the hormone treatment and the placebo I'll calculate that in Excel and then I'll start plugging numbers in here for group a and Group B again the hormone treatment and the placebo now let me flip over to Microsoft Excel I've already typed in some stuff and on the left there you'll notice I've typed in some ABCD stuff and those are just intermediate steps and I'll tell you what those are I also have included the data for both groups the first thing I do is I calculate average I hit the equal sign and type the word average open parenthesis click on the top number which is nine and drag down close parentheses and enter that's my mean or my average now for the placebo hit equal type the word average open parenthesis click on for drag down close parentheses enter and the mean is five point one I can easily change the format the color it's green I'm using green and blue but I can change that any color I wanted to and I'm using large font and then notice I hit a number I can change the number of decimals as many as I want or as few as I want and I'll leave it at two for now okay now I put seven point eight in and five point one I'm going to go back to Microsoft Excel and calculate the standard deviations so I hit the equal sign and type the word st DV so the equal sign stdev open parens click on the nine and drag down close parentheses and that gives me my standard deviation I'll do the same for placebo hit equal St Evie open parenthesis click on for drag down close parentheses and hit the enter key I can use the function count to determine the number of participants so I hit equal and tap the word count open parenthesis click on nine and drag down close parentheses and hit enter it gives me ten I'll do the same thing for sibo close parentheses and also is 10 I'm also going to calculate s squared which is the variance so I take equal and I click on 2.15 and I use that little hat above the six and type two for squaring I'll do that for both and then my variance is there I go now the difference between the two means is easy to calculate is that's that roll right there it's going to be seven point eight equal sign and take seven point eight minus five point one and it enter and wallah two point seven zero let me add that to this equation here so the numerator becomes two point seven there you go I'm going to do this I'm going to take n minus 1 times s square root of the variance and I'm going to do that for both the hormone treatment and the placebo I'm going to call that a so I hit the equal sign and I click on 10 which is N and although minus one times four point six now I need to put parentheses around that see 14 minus one I need to put parentheses around that otherwise the calculation will not be correct and now I hit enter there go 41.6 I'll do that again I'll hit equal 10 which is n minus one times two point seven seven now if I hit enter I don't use the parentheses I get the wrong answer so let me put parentheses around this around the d40 minus one make sure you do that and hit enter again and y la 24 point nine which is the correct value let me put those in the appropriate spots right there and I just took those from the spreadsheet that we just calculated let me add those two values together so I go back over to excel I hit the equal sign and then click on 41.6 plus 24.9 hit enter that 66.5 now I put 66.5 into my equation now I'll calculate the denominator and we'll call that B now we hit the equal sign and I click on the 10 for hormone treatment and then also and I add this to the 10 to the placebo which is the green and blue – – and it enter which is 18 and I put that in my equation so you can follow along so now I will add the blue in plus the green in which is 20 but let me do that in Excel so at equal sign 10 plus 10 is equal to 20 and now I hit enter and I'll put that into my equation hey we're getting there now I do n times n which is 100 and I'll do that in Excel as well so I do 10 times 10 is 100 enter there you go put it into my equation now I'm going to divide 66.5 divided by 18 and 20 divided by 100 now I take 66.5 divided by 18 and hit enter and then I take 20 divided by 100 and this is equal to 0.2 oh so I put in three point six nine and point two oh into my equation now I multiply those two together point 200 times three point six nine hit enter and that is 0.74 now I'll take the square root of 0.7 for so back over to excel now 0.7 for the little hat and then type in 0.5 which means 1/2 hit enter which is 0.8 6 now I take two point seven divided by point eight six two point seven divided by 0.8 six which is equal to three point one four finally and this is really the result I'm looking for is 3.14 now how do we interpret this and what does this mean so I have T T is equal to three point one four n is equal to 10 and n is equal to ten both sample sizes are 10 degrees of freedom is equal to 18 which is n plus n minus 2 I want to be 95% confident and I'm going to do a two-tailed test so I draw my little bell curve and I'm going to determine my critical region or rejection regions I should say critical values so my rejection regions are the red areas and I'm looking for my critical values which are those question marks there these are the values that you'd find in the back of any typical stats test for these 90% 95% and 99% confidence levels and down the side is degrees of freedom 1 2 3 4 5 in my case this case and I'll just put the values there so I have 18 degrees of freedom and I'm a 95% confidence I can draw like a little yellow marker here and where those two there and here where they cross so my critical value is two point one zero one and I'll say two point one and also negative two point one so go back to my results here my normal hypothesis and I assume that these were equal experimental group of my control group I determine my T score was three point 1 4 which is in the rejection region so therefore I reject the null hypothesis rejected and that's how you set up a spreadsheet in Microsoft Excel for a t-test of unrelated means share the knowledge share the love Facebook Google+ Twitter comments and suggestions below and like us please like me and don't forget to subscribe I'm always posting new material you

39 thoughts on “How to Calculate t test Using Excel for Unrelated Groups (Independent groups)

  1. This man deserves the salary of my Professor. This is the best video to understand t-test, degrees of freedom and whether to reject or accept the null hypotheses. This excel method is even much easier than that of STATA which confuses me each time. I should be grateful if you could upload other methods using the excel sheet. Thank you very much Sir!

  2. if i have 2 group of patient control(n=12) and cancer (n=78) and i want to analyse the data of protein biomarker expression level so in that condition what type of Ttest i should use, please help

  3. hey sir. we want to know how did you get the degrees of freedom? please sir we are really in need of your immediate answer

  4. Extremely well explained in a very simple way. Thank you very much sir.You just cleared some of my confusions. I have tested with the formula you mentioned here and tested along with Excel calculations and they are perfect match…Thanks a lot again….

  5. You are an amazing teacher. but how did you calculate those T values? i.e the table and 95% confidence with degrees of freedom? could you please explain more about them?

  6. You saved me then I can submit my research now. Please, try to continue sharing your knowledge. Thank you so much!!!

  7. Min 12.41, how did you calculate those T values ? You did not say anything about it. You simply put the table and say 95% confidence with degrees of freedom goes like this. Where do i get that info ? I'm using minitab, it does says it anywhere ?

  8. Just watched the first two minutes of the video and I gotta say that this graph explains the point of the ttest really well! It's become clear in my mind at last, thanks for your help!

  9. Isn't this slightly incorrect?

    For a two tailed test at alpha = .05 your critical points would be +-t .975 (.05/2)
    For a one tailed test at alpha = .05 your critical point would be +t.95 (.05)

    I think that this is a perfect example of a two tailed t test at alpha = .10 though.

  10. This is fantastic!! I was completely lost before and my book sucks but this explained everything step by step.

  11. not quite the 'using excel' i was expecting, more accurately 'using excel as a calculator.' good explanation of the t test, but not the explanation of using excel's ttest function i was hoping for.

  12. You are an amazing teacher. I've never been too good with stats. but I've learned so much just from watching your vids

  13. very well explained tutorial, but when can you say that there is a (highly) significance between the means in this example, because the p-Value is 5%? Thanks a lot for an answer

  14. Hi,

    Thank you for the video, this was cristal clear. However, I wonder how we can get the final table of the "t values needed for rejection of null hypothesis". For example, I wish to take a 95% degree of confidence with a degree of confidence equal to 80. Do we have to calculate it or can we find it somewhere else?

  15. Thank you so much for this tutorial.  I not only learned a lot, I was impressed with your skill as a teacher.

  16. I am sorry, can I ask how we can have the P value calculated for this test? Is that the P value for the two tailed calculated by the excel?

  17. I'm just wondering why do you need to times the pooled variance denominator with the [(n+n)/(n*n)] (i.e. what is the significance).

    Other than that, GOOD JOB and half of my lab report owe it to your videos 🙂

  18. Hi sir! Can I ask how do you know which confidence level will you use? is it you just randomly choose between 90%, 95% and 99%?

Leave a Reply

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