This Excel / SPSS tutorial explains how to calculate Chi Squared Test of Contingency Table in Excel, SPSS and manual calculation.
You may also want to read:
Excel SPSS Chi Squared Goodness of fit test
SPSS Excel Chi Squared Test of Contingency Table
Chi Squared Test of Contingency Table is used to infer whether two nominal variables in the population are related. In Excel, Contingency Table is a Pivot Table where we have a variable in the column and a variable in row, and count the frequency that falls into the combination. Such table is also known as “cross-tab” or “matrix report”.
Below is an example of a cross-tab table, illustrating how many male and female for different undergraduate degree.
| Observed Frequency | Gender | ||
| Undergraduate Degree | Male | Female | Total |
| Art | 20 | 80 | 100 |
| Engineering | 70 | 20 | 90 |
| Business | 100 | 120 | 220 |
| Science | 90 | 20 | 110 |
| Total | 280 | 240 | 520 |
Chi Squared Test of Contingency Table – Manual Calculation
Using the above table as example, we want to evaluate if gender and the choice of undergraduate degree is related. We create a hypothesis as below
Ho: The the variables are independent H1: The two variables are dependent
If Ho is true, then the number of male and female are same for each undergraduate degree, but it does not mean simply dividing the total by 2 for each degree, because we also have to consider the proportion of gender. In the sample, we have 280 males and 240 females, the proportion for male is 280/520 = 0.538. For female, it is 240/520 = 0.4615.
There are 100 Art students, ideally there would be 100*0.538= 53.8 males and 100*0.4615 = 46.15 females. Now calculate the expected frequency for each undergraduate degree and we have the below result.
| Expected Frequency | Gender | ||
| Undergraduate Degree | Male | Female | Total |
| Art | 53.846 | 46.1538 | 100 |
| Engineering | 48.462 | 41.5385 | 90 |
| Business | 118.46 | 101.538 | 220 |
| Science | 59.231 | 50.7692 | 110 |
| Total | 280 | 240 | 520 |
For each observed frequency and expected frequency, do summation of (Observed Frequency-Expected Frequency)2/ Expected Frequency, the result is Chi-Square value.
| Art Male | 21.27472527 | |
| + | Engineering Male | 9.572649573 |
| + | Business Male | 2.877122877 |
| + | Science Male | 15.98401598 |
| + | Art Female | 24.82051282 |
| + | Engineering Female | 11.16809117 |
| + | Business Female | 3.356643357 |
| + | Science Female | 18.64801865 |
| Total | Chi Square value | 107.7017797 |
The next step is to calculate the probability of Chi Square value to see if it is <0.05. Since we cannot manually calculate the value, we have to use Excel Function CHISQ.DIST.RT. There are two arguments in this function, the first parameter is the Chi Square that we just calculated, the 2nd is the degree of freedom. The degree of freedom of a contingency table is
v = (r-1)(c-1) = (4-3)(2-1) = 3
Now apply the formula
| Formula | Result |
| =CHISQ.DIST.RT(107.7017797,3) | 3.42677E-23 |
Note that 3.42677E-23 means 3.4*10-23
Since the P value is <0.05, we conclude that there is significant evidence that there is a relationship between undergraduate degree and gender.
Chi Squared Test of Contingency Table – using Excel
Given that we have created the below table
There are two arguments for CHITEST Function. The first one is the observed frequency range, the second one is expected frequency range. Now apply the formula
=CHITEST(B3:C6,B13:C16)
We get the result 3.42677E-23
Chi Squared Test of Contingency Table – using SPSS
Since SPSS only allows analysis using numeric values, we need to use number to represent the degree and gender, and then input the mapping in Value Label in SPSS.
For example, we decide a mapping like this
| Numeric value | Mapping |
| 101 | Art |
| 102 | Engineering |
| 103 | Business |
| 104 | Science |
| 1 | Male |
| 2 | Female |
And we have a data source in numeric values
In SPSS, go to the Variable View tab, Values field to input the mapping for both variables.
In the menu bar, navigate to Analyze > Descriptive Statistics > Crosstabs, select the Row and Column as below.
Click on Statistics button and check the box Chi-Square
Click on Cells button and check the box Expected
Finally click on OK to export the result
In the result, you can see the Chi-Square value is 107.702, and the probability is 0.000 since the value is too small to show. The actual value should be 3.4*10-23
