# 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.2747 + Engineering Male 9.57265 + Business Male 2.87712 + Science Male 15.984 + Art Female 24.8205 + Engineering Female 11.1681 + Business Female 3.35664 + Science Female 18.648 Total Chi Square value 107.702

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