How to Use the Chi Square Test Excel Function? 4 Easy Steps
Note: This tutorial on the Chi Square test Excel function is suitable for Excel versions 2010 and later, including Office 365.
The CHISQ.TEST is a statistical function in Excel that calculates the chi-square statistic of two variables in a dataset.
Usually, these two variables are categorical in nature and represent the frequency of occurrence of any human behaviour or natural phenomenon.
The Chi-square test of independence helps us understand whether there is any kind of significant relationship between these two variables.
Hence, it is an excellent tool to test the independence of two categorical variables.
In this guide, I’ll show you how to use the Chi Square test Excel function properly and how to interpret its results correctly.
Related:
The Excel SMALL Function – 3 Best Examples
How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide
How to Use Excel COUNTIFS: The Best Guide
You’ll learn:
- When to Use the Chi Square Test of Independence?
- Chi-Square Test Distribution Formula
- Chi Square Test Excel Function – Syntax
- How to Use the CHISQ.TEST Function in Excel?
- Things to Keep in Mind About the CHISQ.TEST Formula
You can download the practice Excel sheet and follow along with this tutorial.
When to Use the Chi Square Test of Independence?
Use it to determine the independence of two categorical variables.
Some common use cases are:
- Sales Performance (Good and Bad) vs Sales Training (Trained and Untrained)
- Age of Borrowers (Young, Middle Age and Old) vs Credit Risk (High Risk and Low Risk)
- Stock Performance (Good, Bad and Neutral) vs Industry Performance (Good and Bad)
In this example, I’ll test the relationship between gender (male and female) and voting preference (left, right and centre).
Chi-Square Test Distribution Formula
The simplest version of the chi-square distribution formula is given below:
Where,
- ‘r’ is the number of rows.
- ‘c’ is the number of columns.
- Oij is the observed frequency in row ‘i’ and column ‘j’.
- Eij is the expected frequency in row ‘i’ and column ‘j’.
Don’t get intimidated by this formula. You can bypass most of these calculations using the CHISQ.TEST function. All you need to do is to understand the basics of how and where to use this function and how to interpret the results.
Please note that the Chi square test Excel function (CHISQ.TEST) is not available in versions earlier than Excel 2010.
It is an updated version of the CHITEST function.
If the Chi square test Excel function is not available in your Excel version, the Chi-Square statistic has to be manually calculated using the above formula. Then, you have to compare it with the chi-square distribution chart to test its significance.
Chi Square Test Excel Function – Syntax
In Excel versions 2010 and later, there is a built-in function called CHISQ.TEST to quickly calculate the chi-square statistic value.
It has the following syntax:
= CHISQ.TEST(actual_range,expected_range)
Where actual_range is the data range of the cells that contain the observed frequencies.
Expected_range is the data range of the cells that contain the expected frequencies.
Also Read:
How to Add Error Bars in Excel? 7 Best Methods
How to Extract an Excel Substring? – 6 Best Methods
How to Make a Line Graph in Excel? 4 Best Line Graph Examples
How to Use the CHISQ.TEST Function in Excel?
In this guide, I am going to test whether the voting preference of a person is independent of his/her gender.
It is very important to clearly state the null and alternative hypotheses before solving this problem.
For this example,
Hₒ (Null Hypothesis): Gender and Voting preferences are independent.
H₁ (Alternative Hypothesis): Gender and Voting preferences are not independent.
You can follow these simple steps to easily use the Chi square test Excel function and test the independence of two variables.
- Before we begin, arrange your dataset like this:
Notice that the rows contain frequencies of one categorical variable (gender) and the columns contain frequencies of another categorical variable (voting preference). Also, calculate the row and column totals as shown above.
- Then, in a different location calculate the expected frequencies of the same dataset. It is very simple and defined by the formula = (row total x column total) / grand total.
- Using the CHISQ.TEST function is very simple. All you have to do is just enter the ranges of the observed and expected frequencies inside the formula.
In this example, I use the formula =CHISQ.TEST(B3:D4,B10:D11)
That’s all, you will directly get the p-value of the chi-square distribution directly as the result of the above formula.
This p-value represents the probability that the difference between the observed and expected frequencies are caused by mere chance.
Usually, a p-value of less than 0.05 (Alpha – the confidence interval) is considered significant. That is, if the p-value is 0.0125, then there is a 1.25% probability that the differences are due to mere chance. The smaller this value, the higher the significance.
A higher significance value implies that the null hypothesis (Hₒ) is rejected.
Hence, in this case, Hₒ is rejected. This means that gender and voting preferences are significantly dependent on each other.
- You can use an IF formula to compare the p-value and alpha to test the significance and return the accepted hypothesis.
In this example, I use =IF(H7<H6,H3,H2), to determine and return the accepted hypothesis.
Things to Keep in Mind About the CHISQ.TEST Formula
- The #N/A error usually occurs when the observed and expected frequencies are of different dimensions. They should always match and there should be at least two categories for each variable.
- The #DIV/0! error occurs when any of the input values are zero.
- The #NUM! error occurs when any of the input values are negative.
Suggested Reads:
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Superscript in Excel? (9 Best Methods)
How to Split Cells in Excel? 3 Easy Methods
FAQs
What is the Chi square goodness of fit test?
It is similar to the Chi square test of independence. But, unlike the test of independence, we compare the observed frequencies in a sample vs the observed frequencies in the population of the same variable. This test is used to determine if there is any sampling error in an experiment.
What does the p-value mean in the Chi-square test?
The p-value represents the probability that the deviation of values between the variables occurred due to mere chance. A higher p-value means that the deviation in the values can be explained by mere chance and the relationship between the variables is insignificant.
Closing Thoughts
In this tutorial, I have covered all the important details about CHISQ.TEST function. We saw how to use the Chi Square test Excel function appropriately and also how to interpret the results. You can download the attached sample Excel worksheet and experiment with the details to get a better understanding of the concept.
If you need more high-quality guides on Excel visit our free Excel resources centre.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.
Click here for advanced Excel courses with in-depth training modules.
Adam Lacey
Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.
