Excel Instructions: Test of Independence Using Technology

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 problemsModerate to severe risk
Participated793309
Did not participate441110

 

Expected Data:

No risk for alcohol problemsModerate to severe risk
Participated823279
Did not participate411140

 

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).