This tutorial explains how to perform the following three types of Chi-Square tests in Excel:
- Chi-Square Test for Independence
- Chi-Square Test for Goodness of Fit
- Chi-Square Test for Homogeneity
Chi-Square Test for Independence
We use a chi-square test for independence when we want to test whether or not there is a significant association between two categorical variables. The test has the following hypotheses:
Null hypothesis (H_{0}): The two variables are independent
Alternative hypothesis (H_{A}): The two variables are not independent
The test statistic is X^{2} = Σ [ (O_{i} – E_{i})^{2} / E_{i} ]
Where Σ is just a fancy symbol that means “sum”, O_{i} is the observed frequency at level i of the variable, and E_{i} is the expected frequency at level i of the variable.
If the p-value associated with the test statistic is less than our significance level (common choices are 0.10, 0.05, 0.01), then we can reject the null hypothesis and conclude that the two variables are not independent.
Example
Suppose we want to know whether or not gender is associated with political party preference. We take a simple random sample of 500 voters and survey them on their political party preference. Using a 0.05 level of significance, we conduct a chi-square test for independence to determine if gender is associated with political party preference.
The following table shows the results of the survey:
Republican | Democrat | Independent | Total | |
Male | 120 | 90 | 40 | 250 |
Female | 110 | 95 | 45 | 250 |
Total | 230 | 185 | 85 | 500 |
To perform a chi-square test for independence, we need to create a table of observed values, a table of expected values, and a table that calculates the Chi-square test statistic.
The following screenshot from Excel shows how to perform this test:
And the following screenshot shows which formulas were used to produce these values:
The important function we use at the very end to calculate the p-value of the test is CHISQ.TEST, which uses the following syntax:
CHISQ.TEST(actual range, expected range)
- actual range: the range of cells in Excel that contain the actual observed values
- expected range: the range of cells in Excel that contain the expected values
This function returns a p-value for the test.
In this case, since the p-value of 0.649 is not less than our significance level of .05, we fail to reject the null hypothesis. This means we do not have sufficient evidence to state that there is an association between gender and political party preference.
Chi-Square Test for Goodness of Fit
We use a chi-square goodness of fit test when we want to test whether or not a categorical variable follows a hypothesized distribution.
Null hypothesis (H_{0}): The variable does follow a hypothesized distribution
Alternative hypothesis (H_{A}): The variable does not follow a hypothesized distribution
Once again, the test statistic is X^{2} = Σ [ (O_{i} – E_{i})^{2} / E_{i} ]
If the p-value associated with the test statistic is less than our significance level, then we can reject the null hypothesis and conclude that the two variables are not independent.
Example
An owner of a shop claims that 30% of all his weekend customers visit on Friday, 50% on Saturday, and 20% on Sunday. An independent researcher visits the shop on a random weekend and finds that 91 customers visit on Friday, 104 visit on Saturday, and 65 visit on Sunday. Using a 0.05 level of significance, we conduct a chi-square test for goodness of fit to determine if the data is consistent with the shop owner’s claim.
To perform a chi-square test for goodness of fit, we need to create a table of observed values, a table of expected values, and a table that calculates the Chi-square test statistic.
The following screenshot from Excel shows how to perform this test:
And the following screenshot shows which formulas were used to produce these values:
In this case, since the p-value of 0.00495 is less than our significance level of .05, we reject the null hypothesis. This means we have sufficient evidence to say the true distribution of customers who come in to this shop on weekends is not equal to 30% on Friday, 50% on Saturday, and 20% on Sunday.
Chi-Square Test for Homogeneity
We use a chi-square test for homogeneity when we want to formally test whether or not there is a difference in proportions between several groups.
Null hypothesis (H_{0}): The proportion of “successes” in each group is the same
Alternative hypothesis (H_{A}): The proportion of “successes” in each group is not the same
Once again, the test statistic is X^{2} = Σ [ (O_{i} – E_{i})^{2} / E_{i} ]
If the p-value associated with the test statistic is less than our significance level, then we can reject the null hypothesis and conclude that not all of the groups have the same proportion of “successes.”
Example
A basketball training facility wants to see if two new training programs improve the proportion of their players who pass a difficult shooting test. 172 players are randomly assigned to program 1, 173 to program 2, and 215 to the current program. After using the training programs for one month, the players then take a shooting test. The table below shows the number of players who pass the shooting test, based on which program they used.
Program 1 | Program 2 | Current Program | Total | |
---|---|---|---|---|
# Passed | 112 | 94 | 130 | 336 |
# Failed | 60 | 79 | 85 | 224 |
Total | 172 | 173 | 215 | 560 |
Using a 0.05 level of significance, we conduct a chi-square test for homogeneity to determine if the proportion of players who pass the shooting test is the same for each group.
To perform a chi-square test for homogeneity, we need to create a table of observed values, a table of expected values, and a table that calculates the Chi-square test statistic.
The following screenshot from Excel shows how to perform this test:
And the following screenshot shows which formulas were used to produce these values:
In this case, since the p-value of 0.12194 is not less than our significance level of .05, we fail to reject the null hypothesis. This means we do not have sufficient evidence to say that the three programs produce different results.