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

Excel Chi Squared Goodness of fit test 02

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”)

 

Excel Chi Squared Goodness of fit test 01

 

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.

Excel Chi Squared Goodness of fit test 03

 

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.

Excel Chi Squared Goodness of fit test 04

 

Go to the menu tab > Analyze > Non Parametric Tests > Legacy Dialogs > Chi-Square

Excel Chi Squared Goodness of fit test 05

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).

Excel Chi Squared Goodness of fit test 06

Now we have the Chi-Square value as well as the P value.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in 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
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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