SPSS Excel Chi Squared Test of Contingency Table

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

SPSS Excel Chi Squared Test of Contingency Table 01

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

SPSS Excel Chi Squared Test of Contingency Table 02

In SPSS, go to the Variable View tab, Values field to input the mapping for both variables.

SPSS Excel Chi Squared Test of Contingency Table 03

 

In the menu bar, navigate to Analyze > Descriptive Statistics > Crosstabs, select the Row and Column as below.

SPSS Excel Chi Squared Test of Contingency Table 04

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

 

SPSS Excel Chi Squared Test of Contingency Table 05

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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