help with chi square tests in excel

[nuSFwck]

i'm having a problem figuring out some functions on excel. for some homework problems, i have to do some chi-square tests of independence.

here is what i know:
the critical chi-square value is found by "=CHIINV(level of significance, degrees freedom)"

i need to to figure out how to calculate the computed chi square value so i can draw conclusions about brand dependence for a certain problem. what commands/formulas do i need to do in excel to generate this value?

here is a picture of the information i need to use (this may or may not be helpful):
excel picture

[peterchi]

I don't think you want to use the CHIINV function. This takes an inputted one-tailed probability (your desired p-value threshold) and returns the inverse, which would be your expected value at that p-value.

What you want to use is CHITEST. I need to know what your null hypothesis is, though. Is it that the brand selection is completely random? Are you supposed to do this separately in males and females, or are you supposed to consider them together?

[nuSFwck]

Ho: sex and brand choice are independent
Ha: sex and brand choice are related
level of significance: 0.05

reject Ho if X^2 (chi squared) > 5.991
(5.991=CHIINV(.05,2)

that's my critical chi square value.
i need my computed chi square value to decide if i reject or not.
the p-value will be computed as: =CHIDIST(computed chi square value, degrees freedom)
where degrees freedom = (#rows-1)(#columns-1)

does this help? if you need more info, tell me.

[RacersEdge]

This should be a simple formula basde on the values and means of the rows and columns. Don't you have a book - or are you trying to get Excel to spit it back to you just from that matrix?

[nuSFwck]

we never use the book and his lecture/notes supplement is very vague on how to do the calculation.

i'll try thumbing through the book.

[EMcWilliams]

Go to the library at your school and use minitab next time...so much easier, its not even funny.

[peterchi]

ok

You need to compute a table of expected counts.

Expected count = (row total x column total) / TOTAL

So for example, your expected count for males using brand a would be (152 x 110) / 309

Then I would use the CHITEST function...

The thing is, I don't know of a function to give you the chi square statistic itself. The CHITEST function gives you the p-value derived from the chi square statistic right away.

You can see this by doing the CHITEST function over your observed values and calculated expected values. This will return your p-value. If you wanted, you could then use CHIINV and input this value, and this WILL give you the chi square statistic. But the thing is, when you do that, you're working backwards, since you already have the p-value.