In the previous activity, we created a table of expected counts to go along with our table of observed counts. In this activity, we will use both of these tables to conduct a chi-square test on the data.
To do this in Excel, we first need to re-create both the table of observed counts and table of expected counts from the last exercise. Here are the data again for your convenience:
Observed Data:
No risk for alcohol problems | Moderate to severe risk | |
---|---|---|
Participated | 793 | 309 |
Did not participate | 441 | 110 |
Expected Data:
No risk for alcohol problems | Moderate to severe risk | |
---|---|---|
Participated | 823 | 279 |
Did not participate | 411 | 140 |
Now use the CHITEST function in Excel to calculate the p-value given the data in our tables. To do this, pick an empty cell and type =CHITEST([actual range], [expected range]) where [actual range] is the range of observed data in our table (without row/column headers or totals) and [expected range] is the range of expected data in our table (again without row/column headers or totals).
For example, assuming that the top left cell in the table above is A1, the formula would be =CHITEST(B3:C4, B8:C9).