Chi-Square Test is a statistical function, which is used to compute the predicted value given a data set with observed values. In this tutorial, we’ll look at how to use Excel to calculate the Chi-Square from a database.
Let’s look at a few examples before getting into the details of the Chi-Square Test.
Also read: How to Calculate Age in Excel? – A Complete Beginner’s Guide
What Is Chi-Square?
The Chi-Square Test is a method for determining whether or not a hypothesis is valid. The Chi-Square P-Value indicates whether or not the observed results are statistically significant.
We reject the null hypothesis if the finding is statistically significant (the null hypothesis in statistics is a statement or hypothesis which is likely to be incorrect). A P-Value for Chi-Square is an integer between 0 and 1. The null hypothesis is usually rejected when the Chi-Square P-Value is less than 0.05.
How To Do Chi-Square Test in Excel?
Let’s look at some examples of how to do the Chi-Square Test in Excel.
Example 1
Aim: The Chi-Square test can determine whether the proportion of a certain number of items in one location is statistically independent of each other based on a random sample.
Let’s say your organization has a total of 10,000 pieces of furniture. About one-tenth of them are spread among four halls. As illustrated below, we can figure out what percentage of the entire furniture is in one hall:
Keep in mind that each hall has roughly 250 pieces of furniture. If we want to know the expected amount of furniture per type, we may use the following formula:
Expected Value = Category Column Total X (Category Row Total/Total Sample Size)
Using this calculation, we can estimate the number of chairs in Hall A to be around 92:
In Hall A, the expected number of chairs is 250 X (362/984).
We’ll use the following formula to see if there’s a difference:
((Observed Value-Expected Value)ⁿ)/expected value
In the Chi-Square test, n = 2 is used.
This gives us the number 0.713928183. Similarly, we will discover the values for each quantity, and the test statistic will be the sum of these values. If all of the variables are independent of one another, this statistic has a Chi-Squared distribution. For each quantity, the following formula would be used to determine the degree of freedom:
(number of rows – 1)(number of columns – 1)
Which is 6 in this scenario.
For the very first value, the number of chairs, we calculate the Chi-Square P-value. The null hypothesis is that the type of furniture has no bearing on where it is placed.
For the chairs, the Chi-Square P-value would be determined as follows:
If the null hypothesis is true, the sum of all Chi Square P values should be 1. We reject the null hypothesis if the test statistic is too great in the current dataset.
As the preceding example shows, computing Chi-Square and testing for the significance of hypothesized data in statistics is a time-consuming operation that necessitates extreme precision.
The CHISQ can be used to examine the scenario above.
TEST Function to directly obtain the Chi-Square value and determine whether or not our premise that the location of the furniture is independent of the type of furniture is valid. In this instance:
The value of the Chi-Square is roughly 0.03. We already know that this rejects the null hypothesis based on our previous discussion.
Example 2
The following steps must be followed in order to calculate the p-value in Excel:
Calculate the value you expect to receive. For properly distributed datasets, the predicted value in chi-square is usually a simple average or mean. Please see the example above for more intricate data.
- Enter your data into columns, then select a blank cell on the worksheet where you wish to display the results, and afterwards select the “Insert Function” option on the toolbar, which will bring up a pop-up window. In the Search for a Function box, type chi and then press “Go.” then click “OK” after selecting “CHITEST” from the list.
- Click “OK” after selecting the observed and expected ranges.
- As a result, we will have the following outcome.
Note: CHISQ functions can also be entered straight into a cell, just like every other function. If you already know the data ranges you’ll be working with, this will save you time.
Conclusion
That brings us to the end of this short article on Chi square. There’s a lot you can do by exploring this function in detail. As you uncover more opportunities to use this method, you can work out more valid use-cases for it in your work-life.