How to Perform Chi-Square Tests in Excel

Chi-Square tests in Excel

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 (H0): The two variables are independent

Alternative hypothesis (HA): The two variables are not independent

The test statistic is X2 = Σ [ (Oi – Ei)2 / Ei ]

Where Σ is just a fancy symbol that means “sum”, Oi is the observed frequency at level i of the variable, and Ei 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:

Chi square test for independence in Excel

And the following screenshot shows which formulas were used to produce these values:

Chi-square test for independence critical value calculation

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 (H0): The variable does follow a hypothesized distribution

Alternative hypothesis (HA): The variable does not follow a hypothesized distribution

Once again, the test statistic is X2 = Σ [ (Oi – Ei)2 / Ei ]

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:

Chi square test for goodness of fit in Excel

And the following screenshot shows which formulas were used to produce these values:

Chi square test in Excel with formulas shown

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 (H0): The proportion of “successes” in each group is the same

Alternative hypothesis (HA): The proportion of “successes” in each group is not the same

Once again, the test statistic is X2 = Σ [ (Oi – Ei)2 / Ei ]

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:

Chi square for homogeneity in Excel

And the following screenshot shows which formulas were used to produce these values:

Chi square test for homogeneity in Excel

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.

Leave a Reply

Your email address will not be published. Required fields are marked *