Welcome In this video, I’m going to create a chi-square
test of independence solution for this 4 by 4 contingency table
and also create templates for conducting tests for tables of other dimensions
The null hypothesis for this test will be that the two variables are independent and
the alternative will be that they are dependent.
I’ll start by taking row totals So =SUM() for the first row
and drag the handle down for the others Same for column totals
=SUM( ) for first column drag to the right for the rest.
Next I’m going to copy the Observed table and paste for Expected frequencies table here
…and delete the observed frequencies
The expected frequencies can be calculated by taking row total times column total divided
by overall total.
So for the first cell, = the row total times the column total
divided by the overall total
Now, to ensure that I can drag that correctly to other cells, I need to lock down some values.
I’m going to double-click the cell type a dollar sign before F to lock down column
F (for the row totals) then type a dollar sign before 7 here to lock
down row 7 for the column total then make F7 absolute cell by pressing F4
or entering dollars sign before F and before 7.
Enter and drag to the right then down
for the other cells
I will double-click on a cell to ensure the correct cells are selected
and also check to ensure that the expected totals are the same as the observed totals.
Next, I’m going to calculate the chi-square statistic using this formula.
I’ll copy the observed table without the totals and paste to the right here.
Label it Calculations and delete the frequencies In the first cell, I’ll select the first
observed minus the first expected, squared, and then divide by the first expected.
Enter Drag to the right
then down The observed chi square statistic (which I
call chisq.obs) is thus the sum of all these values.
Now, in the spirit of setting up a template, I’ll count the rows by selecting the first
item of each of the rows And for columns, count the number of items
in first row of the columns.
The degrees of freedom for the test is (# rows -1)*( # columns – 1)
and let’s suppose we have a significance level alpha of .05.
I’m going to colour the observed cells yellow and do the same for the alpha value
Those will indicate the only cells we need to change once the template is ready.
I’m going to strategically leave the next two rows here blank
(you’ll see why later) I’ll place the chi square critical value
here The function is CHISQ.INV.RT
with inputs alpha, comma, degrees of freedom.
Then ENTER That is, if our test statistic is greater
than 16.919, we will reject the null hypothesis.
I’m going to place the observed chi square statistic calculated earlier here
Since 14.5 is not greater than 16.9, we fail to reject the null hypothesis
and therefore cannot conclude that Group and Area are dependent.
Next, we have the p-value which is calculated using the CHISQ.DIST.RT
function
The inputs are chi square statistic, comma, then degrees of freedom.
Since the p-value is not less than alpha, again we cannot reject the null hypothesis.
We can also obtain the p-value directly from observed and expected frequencies by using
the CHISQ.TEST function.
We select the observed or actual values, comma, then the expected values.
Enter
This template is now ready.
I’m going to label the sheet 4 by 4 since we have 4 rows and 4 columns here.
The purpose of creating templates is so that whenever I need to conduct any contingency
table test, I will simply need to enter the observed frequencies here, change the significance
level if necessary, and all the calculations will be done automatically.
Next, I’ll create templates for a few other table dimensions
I’m going to duplicate this sheet by right-clicking on the sheet tab, Move or Copy
Check the Create a copy box Click OK.
I’m going to make this one a 4 by 3 template.
That is, 4 rows and 3 columns.
So all I need to do is delete 1 column.
I’ve set it up so that I can always delete either column 2 or 3,
or row 2 or 3 or both, without affecting any of the formulas.
I’m going to delete the second column by right-clicking on Column C and clicking delete.
I’ll delete column I here as well.
As you can see, we have 4 rows, 3 columns, 6 degrees of freedom and everything else is
calculated.
Here again, we can’t reject the null hypothesis because the test statistic is not greater
than the critical value or the p-value is not less than .05
I’m going to make a copy of this sheet too to create a 3 by 3 template.
I can easily do it by deleting row 2 or row 3.
I’m going to delete row 2 up here and delete it down here too.
We now have a 3 by 3 template.
Now, creating a copy for a 2 by 2 I’m going to delete the middle column here
and here too Then I’m going to delete the middle row
up here And down here too.
This result is statistically significant at alpha = 0.05
In this case, we will reject the null hypothesis and conclude that the two variables are dependent
Now, suppose I want to extend the 4x4 table to, say, 4x5.
I can insert a column between columns 2 and 3
Drag Area 2 all the way to the end I’ll enter some values here
Drag to copy the SUM formula Copy the formula in column 2 to the rest
I’ll do the same for the calculations table Insert
highlight the second column and copy to the rest
We now have a significant result here after the update.
And that’s it for this video.
Thanks for watching.