How To Calculate Chi Square In Excel?
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.
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.
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 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.
