Computing chi square in Excel

June 28, 2019

well this is dr. Frederick with a question from a student about performing chi-square in excel it's not easy to do a chi-square analysis in Excel basically to do chi-square in excel we have to understand what chi-square is now students says these followed the video exactly and doesn't get the right answer and unfortunately a student isn't doing everything that the video did correctly and it's a it's no reflection on the student it's just hard to to keep these concepts straight what the what the video actually does is to show you how to compute a p-value which is the probability of seeing and except I square of that magnitude or higher and so p-value is is not the same thing as a chi-square value so the students say well I'm getting a value that's completely different than the chi-square reported for the quiz here's a problem from the sample quiz and we have basically six data points here here is our six data points we have two groups each of who got a different message about littering and this is the outcome of what happened to the message about littering and so we have this distribution of outcomes for 200 Flyers that were distributed this is the observed so let's just write over here this is observed okay and what we want to make is an expected distribution if there's no relationship between the message and the outcome that's our hypothesis is it that there is no relationship between the message and the outcome that would be the null hypothesis okay so we want to get our expected values and that's done by what's taking the marginals here and multiplying them and then dividing by the totals so the expected value for cell 1 1 is column 1 times let's try it again column 1 times the total for column a row 1 and I want aminute I'm just going to lay my cursor over that and hit f4 and put some little dollar signs in that yep let's try it again I'll kind of get you Bill see it so I'm going to go times the row total now I'm going to lay my cursor over this and hit f4 and now I'm going to hit / now what happened it put dollar G dollar 6 it says I don't want to change that value for future calculations and then I'm going to divide by the total and again I'm going to lay my cursor over this and hit f4 okay so that's Oh I'm supposed to do this calculation here I don't know what happened okay so let's do it again times take the column total times the row total and then we hit f4 divided by the total and I'm going to hit f4 for this again okay now hit enter and there's my expected value for Row one column one now because I put those dollar signs in there I can just now hit copy and paste into these cells and the only value that changed was here d8 went to e8 went to f8 and the other value stayed the same so I went here this times this divided by this and now I want to go this times this divided by this these two things don't change so I use that f4 function says don't change that value but otherwise shift it by one every time we do it okay now let's do I just hit escape to get that little indicator off okay now I'm going to hit equals column total times Row two total and again I'm going to hit highlight and hit up four and then divide by the total for ourselves okay and then I'm going to hit up for on that okay there's the expected value for cell to one okay now I'm going to copy that and then I'm going to paste it right here and again all it does it escaped all it does is you know just shifts it by one okay now go to the next column and multiply here to right here next go the next column okay so now when you look at your video in the course you see this is what they used let's just highlight these these are the observed values these are the expected values and we're going to do some comparison of these now what they did in the video was use the formula Club equal tie test right here okay and then it says okay I'm going to grab these data that's my first one comma that was my observe range now here's my expected range in parentheses and and that is not a chi square body that's what the students misunderstanding is that is not a chi square value I don't know that it's L how's the formula to compute Chi square for you this is the probability of observing whatever Chi square exists for these data or higher what does that mean it means that we don't have a significant Chi square we're not going to reject the null hypothesis to reject the null hypothesis this value this p value remember in the video he type p here to remind you it to p value the p value has to be point oh five were lower for us to reject the null hypothesis so that's what the video is calculating the p-value wasn't very helpful for the assignment was it so let's now do observed minus expected that's that's what we actually have to do so we're going to say alright I want to take this value I'm sorry it said the equal sign for let's hit equal take that value subtract this value observed minus expected now we'll just do that for all six squares okay we're just going to copy that in there and this is the nice thing about cell so you just shifts the calculation now this is what it's a six minus e 10 and this will be F 6 minus F 10 and this will be D 7 minus D 11 okay so it just shifts those calculations so now we have this and what we want to then have is observed I'll just do oh minus e squared we want to square those values okay so put equal and squaring there probably some square function in it so I don't know it I just multiply them by themself okay all right so that's this value squared copy alright there's all the observed minus expected value squared and what is Chi square PI square is all of these values added up so we'll put down Chi square here let's just move that a little bit there and then we'll say it's equal to the sum open parenthesis of all these values in parentheses okay so our chi-square seventy eight point five so I don't think so alright so what did I do around here oh we're not done yet it's o minus e squared divided by E alright so sky squares to sum of all that so let's do it again so o minus e squared I have I want to divide it by e here's e the expected value let's do it again so it's equal to this value divided by this value okay let's see it's not easy to do is it okay now we're going to repeat that now we'll type in Chi square and it's equal to the sum of those six values so sum open parenthesis capture the values in parentheses and that is our Chi square alright so now what we really want to know is is that significant well we already saw that it wasn't because if we did the chi-square test let's do Chi test we're going to grab these data let's just repeat it write a comma grab the next the expected values in parentheses alright that's our p value or P bar says it's not significant so what does that mean for you practically means if we want to get the critical value of Chi square is the critical value going to be bigger than or less than that number right it's going to be bigger than this number in other words Chi Square is only significant when its value is the same or more than the critical value so the critical value must be much higher than 2 point 6 4 because this p value is very high all right how could we find the critical value of Chi square you know using technology let's say um well let's just think about it we might want to find the critical value so let's let's go to Google and we'll do a chi square now here we go chi square critical value calculator all right and here's one I've used it before obviously and we want to know our degrees of freedom so let's see degrees of freedom degrees of freedom is equal to armed minus 1 times C minus 1 so how many rows are there there's two rows but R minus 1 is 1 how many columns are there two so we want to know the critical value when we have two degrees of freedom and alpha is 0.05 calculate 5.99 all right so let's look at it a different way because I think it might make sense to you let's just do a chi-square calculator or with your freaky value calculator for the line so I have two degrees of freedom if I have two degrees of freedom let's just calculate that a chi-square of eight has a probability of less than 0.05 that's you know if that's my chi-square I have a significant I squared it's greater than that expected under the null hypothesis and let's just work backwards so let's go to 6 and calculate oh very close to 0.05 here and so since I know it's 5.99 just put it in there and what there's the critical value it's the point of which the probability of this value or higher when the degrees of freedom is 2 is is 5% okay that's what the critical value means so we probably have all the information we need to solve problems in this homework by understanding how chi-square is computed we start out with our observed values we determine what our expected values are we subtract our expected values from our observer but it doesn't matter which one you subtract from the other actually because you're going to square them and then you're going to divide by the expected value that's the step I missed earlier and once you get these these values you add them up to get your chi-square and you can use a calculator you can use a table to find your critical value by determining what your degrees of freedom are and then what the actual chi-square test in excel have to do with finding probabilities but why why they don't have a formula just to determine chi-square maybe they do and I just don't know it but I've always done it like this okay hope that's helpful

You Might Also Like


  • Reply riya sharma June 28, 2019 at 5:49 pm

    thank you sir …you are helping so many kids like me

  • Reply Arun John June 28, 2019 at 5:49 pm

    Thank you sir

  • Reply Edward Gotham June 28, 2019 at 5:49 pm

    Great video. Thank you so much for going through it slowly step by step, even though as some other comments noted, Excel can do it, this effectively showed me how it works, and how to do it.

    A side note: you may wish to clean the fans on your computer. 🙂

  • Reply JORDAN CLARK June 28, 2019 at 5:49 pm

    Chi calculate in excel is =CHISQ.INV.RT(P-value,degrees of freedom)
    Chi critical in excel is =CHISQ.INV.RT(alpha, degrees of freedom)
    all you need is observed and expected and you can calculate p-value and then the other needed info. It's quicker and easier, and less chances to mess it up.

  • Reply Saravanan Velusamy June 28, 2019 at 5:49 pm


  • Reply Grigs June 28, 2019 at 5:49 pm

    Thanks for this video. Helped tremendously on a problem set for my stats class

  • Reply dman john June 28, 2019 at 5:49 pm

    Thanks a lot. This was very helpful for my assignment.

  • Reply vioscios June 28, 2019 at 5:49 pm

    x^2 for squaring.

  • Reply Colin Banfield June 28, 2019 at 5:49 pm

    To compute critical value in Excel, use CHIINV or CHISQ.INV.RT (Excel 2010+)

  • Leave a Reply