How to Calculate Chi Square in Excel

In this Excel tutorial you will teach yourself how to calculate Chi Square test is Excel.

Why to use?

Chi Square test is used to assess the relationship between qualitative variables (unmeasurable), and there are many of them in research economic.

Having big cap and small cap shares in our portfolio let's check the hypothesis using Chi Square test in Excel. We investigate whether there is a relationship between company size and the propensity to pay dividends.

chi square test dividend data

Chi square calculations

Then you need to calculate the theoretical values.

To calculate theoretical values for big cap companies you need to multiply total number of big cap companies and total companies which did not pay out dividends. Then divide the number by total number of companies.

chi square test theoretical values formula

Theoretical values formula is =B$4*$D2/$D$4.

For other companies, calculate it using the same pattern.

I used absolute reference in the formula.

Having theoretical values you are able to perform Chi Square test.

Use CHISQ.TEST Excel function for that purpose.

chi square test formula.jpg

Chi Square test formula is =CHISQ.TEST(B2:C3,B7:C8)

  • B2:C3 is for current values.
  • B7:C8 is for theoretical values as you calculated minute before.

In my example Chi Square Test equals 0.082 which is more than significance level of 0.05.

It means that the willingness to pay dividends in 2020 did not depend on the size of the company.

You can download the spreadsheet from here.

Further reading: 
How to calculate z score? 
How to calculate p value?
How to calculate Monte Carlo simulation?