# Excel SPSS Chi Squared Goodness of fit test

This SPSS/Excel tutorial explains how to use Chi Squared Goodness of fit test in Excel, SPSS and manual calculation.

You may also want to read:

SPSS Excel Chi Squared Test of Contingency Table

## Chi Squared Goodness of fit test

Chi Squared Goodness of fit test is used to test hypotheses about a population proportion of nominal data. A coin flipping experiment has the chance of tossing head and tail (p=0.5), we call the experiment binomial experiment, where we only have two possibilities and each trial is independent. For Chi Squared Goodness of fit test, it is a multinominal experiment where more than 2 possibilities can happen for each trial. We can use Chi Squared Goodness of fit test to evaluate if there is significant evidence of a proportion change.

## Chi Squared Goodness of fit test – Manual Calculation

Assume that in 2010, supermarket A has the market share of 45%, supermarket B has 40%, supermarket C has 15%. In 2016 we conduct new survey to see if customer preferences have changed. Of the 200 customers we asked, 102 selects supermarket A, 82 selects supermarket B, 16 selects supermarket C. To evaluate if the proportion has a significant change, we create hypothesis:

```Ho: p1 = 0.45, p2 = 0.4, p3 = 0.15
H1: At least one pi is changed```

If  the proportion has not changed, then in the 2016 survey, frequency of supermarket A should be 90 (200*0.45), B to be 80 (200*0.4), C to be 30 (200*0.15).

Apparently the actual frequency would not be exactly the same as the expected frequency, the point is we will see if the difference is significant.

Create a table as below and do some calculations for the blue columns.

 Observed Frequency (2016 survey result) Expected Frequency (if no change) Difference square of difference/ expected frequency Supermarket A 102 90 12 1.6 Supermarket B 82 80 2 0.05 Supermarket C 16 30 -14 6.53 Chi Square = 1.6+0.05+6.533333 = 8.1833333

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 Chi Squared Goodness of fit test is

`v = n-1`

In our example, we have 3 supermarkets, therefore degree of freedom is 3-1 = 2

Now apply the formula

 Formula Result =CHISQ.DIST.RT(8.18,2) 0.016711358

Since the P value is <0.05, we conclude that there is significant evidence to infer that proportions have change in 2016 survey.

## Chi Squared Goodness of fit test – using Excel

Assume that your source data is like this Use CountIf Function to create a summery table

 Observed Frequency (2016 survey result) Expected Frquency (if no change) Supermarket A =COUNTIF(A2:A201,”Supermarket A”) =COUNTIF(B2:B201,”Supermarket A”) Supermarket B =COUNTIF(A2:A201,”Supermarket B”) =COUNTIF(B2:B201,”Supermarket B”) Supermarket C =COUNTIF(A2:A201,”Supermarket C”) =COUNTIF(B2:B201,”Supermarket C”) Now we can directly calculate the probability using CHITEST function.

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

 Formula Result =CHITEST(B2:B4,C2:C4) 0.016711358

## Chi Squared Goodness of fit test – using SPSS

The input requirement of SPSS is quite different from Excel.

First, you need to have data source in numerical value instead of nominal value Supermarket A B C. Assign each supermarket a number, for example, Supermarket A represents 1, B for 2, C for 3. Then go to the Variable View tab, Values field to add a supermarket label for 1 2 3. This step is optional but it makes you easier to read the output file. Go to the menu tab > Analyze > Non Parametric Tests > Legacy Dialogs > Chi-Square Move the “Observed Frequency” to the Test Variable List

At the bottom, type the Expected Frequency percentage in order of Supermarket A B C (because we have named them as 1,2,3 so the proportion has to follow the ascending order of numerical value). Now we have the Chi-Square value as well as the P value.