Hello and welcome to a video on chi-squares using Excel. Today we're going to be talking about
how to perform a chi-square analysis. So first let's take a look at the formula for chi-square which is
in the center of our screen here. This is the symbol for chi-square and
then we have equals the sum of in parentheses observed minus expected squared
all over expected. So we're going to find this value and then sum it up.
O stands for observed frequencies and east stands for expected frequencies. So what are our
observed frequencies? Well that's typically the data that we already have in
a contingency table. A contingency table is a two by
two or three by three or some other arrangement of numbers
in a table form such as this. Today we have a two by two contingency table that we'll
be using and it's two by two because it is two rows and two columns.
Now the first thing that we'll need to do now that we have our observed values
is find out what exactly are our expected values.
So to do that we'll first
need our marginal totals. So to find the marginal total you just add the column
that you're looking at. So 10 is the marginal total for this column. I'm just going to click
and drag this over. So the marginal total for this column is also 10.
All right. Now you
can see we've got the same numbers
over here that we do over here. We're just going to do this exact problem step by step.
So now that we have our marginal totals we can find our expected values.
We find our expected values. By multiplying our marginal totals together and dividing
them by the total of those marginal totals. So for example,
for this first box, we'll look at the same box up here. So we're in low, low, we look at low,
low. And that's a seven. So the marginal
total for seven, there are two, is one of them is 10. The other one
is 11. So we'll take 10 times 11
and divide it by 20. 20 is the total of all of our marginal
totals. And we get 5.5. Let's do the same thing
again, but for the next one. So now we have low high. Low high is 4 up here. Our marginal
totals you're 10 and 11 once more. So we're going to multiply those together and divide
by 20.
Okay, let's do that again. Now we're on high
low. High low is 3. So we're going to
take 10 times 9 divided by 20.
Okay, now one more time 10 times 9 divided
by 20. Okay, now we have our expected values. We can see these are the same ones
that we got before for this problem. So we're still still on track. The next thing we need to find
is observed minus expected squared. This
top part of our problem right here. Okay,
so the observed value, let's
open up a formula equals. Our observed value,
oops, do this in parentheses, observed value,
minus expected, or sorry, we did that backwards. Observed value,
minus expected value, raised to the power of to hit
enter, get to 0.25, one more time, observed value,
got to put that in parentheses. Observed value, minus expected value, raised
to the power of two, one more time,
this time I'm going to remember the parentheses. Observed value, minus expected value,
raised to the power of two, okay, and now
our final one, observed, minus expected raised
to the power of two.
So now that we have this top part of our formula, we can divide it by expected. So
now we're going to have observed minus expected, raised to the power of two, divided
by expected. To do that, all we have to do is
take this value and divide it by our expected value. Let's do that one more time
for each box, divided by expected. You're going to line up your
boxes this way, tables,
divided by expected.
Okay, so we've got our
observed minus expected values squared, and then divided by expected right here.
Now, to finish our chi-square, all we need to do is sum these
numbers together. Okay, and we
got 1.8182, which is the same number we got over here. Now, if you wanted to
know the significance of this number, there are two different ways you could do that. The first is you could consult
a table for chi-square and plug in your degrees of freedom and this number,
the other is that you could use Excel to find the p-value.
And it has two different functions that you can use for this. The first is chi-test,
which is an outdated function, but it does still work, and the second is chi-square.test.
So, if you have a newer version of Excel and it long no longer has chi-test, chi-square.test works
the exact same way. Now, to use chi-square.test, it acts as for our actual range, it
means our observed range comma expected range. So, you
just select all of your expected values, and then you close up where at the C. And
you get 0.17753. You can see we've got a couple different options for chi-square
testing here, just out of
curiosity. Let's see what they do. No, we're not
doing that. Okay. I was hoping maybe we would find something it could do a chi-square
for us, but that does not seem to be the case. Okay, this is all that you need to know how to do in order to do the chi-square
for your homework once you've collapsed your variables. Please let us know if you run into any problems doing
the chi-square. Um oh one more thing before I let you go. Uh the p-value here.
We interpret this in one of two ways. You either reject the null hypothesis, or you fail to reject
the null hypothesis. And you'll know from your homework and from the class that we
reject the null hypothesis, if that p-value is less than 0.05, if our alpha is
0.05. And if our alpha is 0.05, and it's greater than 0.05,
than we fail to reject the null hypothesis. Okay, you should be all set.
Have fun learning Excel.