In this video, I want to show you how to calculate p value for a chi-square test. So here's the results of our experiment. We have three groups of patients, Group I, Group II, Group III. We can imagine that they all received different types of treatment. And for sum process, we noted whether they improved, showed no change or actually worsened as far as some disease conflicts is concerned. So we look down Group I and we notice that 56 patients improved, 30 showed no change, and 10 actually worsened. And on the other hand, Group III, we would notice that 21 improved, 25 showed no change, and 27 actually worsened. So these are the absolute numbers of patients that fall in this group. So this is called a contingency table, we just count the occurrences of this variable which is change in disease for the three groups. So, what we can do here is a chi-squared test for independence, and this is the way that we'd go about it. So first, let's look down Group I. How many patients were in Group I? That's easy to do, we're going to use a sum function. SUM, that is, we can select that. And just for short, I'm going to click all and drag all the way down. So we have C2, C3, C4. Hit the return key or hit the little check mark there and we see there were 76 patients in that group. Now I can move my cursor to the right bottom and that changes to that little black cross and I can drag all the way across. Remember these are relative cell references. So all it's doing, it's counting one, two, three up, one, two up, and one up. So when I go here. If I hold on my Mac Ctrl+U, it's different for other systems Windows and Linux, anyway I can see some D2 to D4 and it's going all the way across to this side. Now the next thing we have to do is just to calculate how many patients actually improved, let's put that here so that equal SUM. I'm going to do this with the click, hold and drag all the way across because I want all three of those to be added. And if I have to turn or enter I see there were 80 patients. Instead of dragging down let me do this longhand. SUM, so it's sum equals sum. And we add all of those and we see that's 86 and we do it here SUM and click on SUM and I'm going to drag across, if you do this by hand, remember open and close parenthesis and there we go. The last thing is I want the total sum of this whole group, which will either be the sum of these three or the sum of those three, it doesn't really matter. That is actually exactly the same. So sum, let's do it down this way, the sum of those three, and we see all in all we had 224 patients. This is counting up all of these. So I have the column totals, 36 plus 30 plus 10 is 76. 21 plus 24 plus 27 is 73. And then the row totals, how many improved? How many did not sure any change? And how many actually wasn't? Now this is our observed table, we did the experiment and this is what we observed. Given that everything is equal, what could we expect from column totals of 76, 75, and 73 and row totals of 80, 86,and 58 in a total of 224 patients. From that we construct our expected table. Now, let me show you how this is done. We've gotta keep paying attention when you do this, it's easy to make a mistake. So I'm in row 1, column 1 for this little table. So I'm going to say equal the column total for column 1 times the row total for column 1. It's row 1 and column 1 because we are dealing with cell row 1 column 1, divided by the absolute total of all the patients equals and we get 27.14. Let me show you again, so now we are in column number 2, store row number 1. So that's going to equal column total 2 times row total 1 divided by the total number. One more time, equals, we are now right here in column 3, row 1. So it's column 3 total times row 1 total, divided by overall total. And there we go. The first row is completed. Let's move to still column 1 now, row 2. So that's going to equal column 1 total times row 2 total divided by overall total. Next one, equals each column total times each row total divided by absolutely total. One more time. And we can use the dollar notation and drag things across, but I just want you to get used to where these totals do come from. So that's going to equal this column, times, it is in row 2, divided by sum total. There we go. Last one, equals its column, times it's in the third row, so it's third row, divided by total. Equals, each column total, times each row total, divided by overall total, equals its three column three row three divided by overall total and there we go. We have our expectation table here. So how do we do the p value now? Now we can just say equal CHISQ, CHISQ and there we see CHISQ.TEST and it says one the actual range and the expected range. So that was our expected range, I'm going to click hold and drag over just the values not the totals, comma and then the expected values which is down here. And hit return, and there we see a p-value 0.009, so that is statistically significant if our alpha value was 0.05. So, that is how to do chi squared tests using spreadsheet software.
