Hi everyone,
I'm teaching statistics and started incorporating Excel alongside R. When we got to running a Chi-Square test, I noticed something very odd that I cannot figure out. Can someone tell me why I get different results with the same formula function.
Note, I'm using Excel 365 on Windows 10.
<tbody>
</tbody>
<tbody>
</tbody>
When I use =CHISQ.TEST(B2:D3,B8:D9) I get a p-value of 0.4051 which is correct.
BUT.....
If I rearrange the tables to look like below, I get a different p-value.
<tbody>
</tbody>
When I use =CHISQ.TEST(I2:I8,J2:J8) I get a p-value of 0.8741 which is not correct.
What's going on that I am missing? Does it have something to do with how the degrees of freedom are being counted?
I didn't see an function option to specify a Goodness of Fit or Test for Independence, which would change how df is counted.
Is there a function to produce the Chi-Square statistic?
At first I thought it was =CHISQ.TEST(), because =CHISQ.DIST.RT also gives me the p-value if I provide it the Chi-Square statistic and the degrees of freedom.
When I start to type =CHI all of the options that pop-up have something to do with the probability and not providing the statistic.
I don't see Chi-Square listed in the Data Analysis Tookpak.
Thanks for the assist!
Shawn
I'm teaching statistics and started incorporating Excel alongside R. When we got to running a Chi-Square test, I noticed something very odd that I cannot figure out. Can someone tell me why I get different results with the same formula function.
Note, I'm using Excel 365 on Windows 10.
A | B | C | D | E | |
1 | Observed | past | present | future | row.total |
2 | up | 12 | 45 | 78 | 135 |
3 | down | 23 | 56 | 89 | 168 |
4 | col.total | 35 | 101 | 167 | 303 |
<tbody>
</tbody>
A | B | C | D | E | |
7 | Expected | past | present | future | row.total |
8 | up | 15.59 | 45.00 | 74.41 | 135 |
9 | down | 19.41 | 56.00 | 92.59 | 168 |
10 | col.total | 35 | 101 | 167 | 303 |
<tbody>
</tbody>
When I use =CHISQ.TEST(B2:D3,B8:D9) I get a p-value of 0.4051 which is correct.
BUT.....
If I rearrange the tables to look like below, I get a different p-value.
H | I | J | |
1 | observed | expected | |
2 | up past | 12 | 15.59 |
3 | up present | 45 | 45.00 |
4 | up future | 78 | 74.41 |
5 | down past | 23 | 19.41 |
6 | down present | 56 | 56.00 |
7 | down future | 89 | 92.59 |
8 | total | 303 | 303 |
<tbody>
</tbody>
When I use =CHISQ.TEST(I2:I8,J2:J8) I get a p-value of 0.8741 which is not correct.
What's going on that I am missing? Does it have something to do with how the degrees of freedom are being counted?
I didn't see an function option to specify a Goodness of Fit or Test for Independence, which would change how df is counted.
Is there a function to produce the Chi-Square statistic?
At first I thought it was =CHISQ.TEST(), because =CHISQ.DIST.RT also gives me the p-value if I provide it the Chi-Square statistic and the degrees of freedom.
When I start to type =CHI all of the options that pop-up have something to do with the probability and not providing the statistic.
I don't see Chi-Square listed in the Data Analysis Tookpak.
Thanks for the assist!
Shawn