The chi-square test is a non-parametric test that compares two or more variables from randomly selected data. It helps find the relationship between two or more variables. In Excel, we calculate the chi-square p-value. Since Excel does not have an inbuilt function, mathematical formulas are used to perform the chi-square test. Show You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked Table of contents
There are two types of chi-square tests which are listed as follows:
#1 – Chi-Square Goodness of Fit TestThe goodness of fit test helps determine whether the sample data matches the population or not. In other words, it shows how well the sample data fits a set of observations. The symbol of the chi-square test is “x2” (i.e., “x” raised to the power 2). “x2” is the summation of the (observed count–expected count)2/expected count. The formula of the chi-square goodness of fit test is given as follows: Where,
The Uses of the Goodness of Fit TestThe test is used in the following situations:
#2 – Chi-Square Test for IndependenceIt helps determine whether the variables are independent of one another or not. Two random variables are called independent if the probability distributionProbability DistributionProbability distribution could be defined as the table or equations showing respective probabilities of different possible outcomes of a defined event or scenario. In simple words, its calculation shows the possible outcome of an event with the relative possibility of occurrence or non-occurrence as required.read more of one variable is not affected by the other. The formula of the chi-square test for independence is given as follows: Where,
The formula for calculating the expected frequency in the ith row and jth column is given as follows: The Uses of the Chi-Square Test for IndependenceThe test is used in the following situations:
The Characteristics of the Chi-Square TestThe features of the chi-square test are listed as follows:
Note: In the simplest form, the chi-square distribution is the square of the standard normal distribution. How to Perform the Chi-Square Test in Excel? (With Example)You can download this Chi Square Test Excel Template here – Chi Square Test Excel Template A restaurant manager wants to find the relationship between quality of service and the salary of customers waiting to be served. She organizes the task in the following way:
She constructs the following hypothesis:
The manager divides the customers into three categories based on their salaries–“low,” “medium,” and “high.” The level of significance (α) is 0.05. The findings are presented as nine data points shown in the following table. Let us calculate the sum of all the rows and columns. We apply the following SUM formula to add the numbers of the fourth row. “=SUM(B4:D4)” Press the “Enter” key and the sum appears in cell E4. The output is 26. Similarly, we apply the SUM formulaSUM FormulaThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more to the remaining rows and columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell.read more. There are 27 respondents with medium salary and 51 respondents who rated the service quality as “good.” We apply the formula “(r-1)(c-1)” to calculate the degrees of freedom (df). df=(3-1)(3-1)=2*2=4 We apply the following formula to calculate the expected frequency for column B and row 4. “(=B7*E4/B9)” The calculation is shown in the following image. The expected number of customers who have “low” salary but rated the restaurant service as “excellent” is 8.32. In the following calculations, E11 is the expected frequency of the first row and the first column. E12 is the expected frequency of the first row and the second column.
Similarly, we calculate the expected frequencies for the entire table, as shown in the succeeding image. Let us calculate the chi-square data points by using the following formula. Chi-square points=(observed-expected)^2/expected We apply the formula “=(B4-B14)^2/B14” to calculate the first chi-square point. We copy and paste the formula to the remaining cells. This is done to fill values in the entire table, as shown in the following image. Let us calculate the chi-square calculated value by adding all the values given in the succeeding table. The chi-square calculated value is 18.65823. To calculate the critical value, we use either the chi-square critical value table or the CHISQ formula. The formula “CHISQ.INV.RT” contains two parameters–the probability and the degrees of freedomDegrees Of FreedomDegrees of freedom (df) refers to the number of independent values (variable) in a data sample used to find the missing piece of information (fixed) without violating any constraints imposed in a dynamic system. These nominal values have the freedom to vary, making it easier for users to find the unknown or missing value in a dataset.read more. The probability is 0.05, which is a significant value. The df is equal to 4. The chi-square critical value is 9.487729037. Let us find the chi-square p-value with the help of the following formula. “=CHITEST(actual_range,expected_range)” We apply the formula “=CHITEST(B4:D6,B14:D16).” The chi-square p-value is= 0.00091723. The chi-square calculated value is significant when equal to or more than the chi-square critical value (tabulated value). The null hypothesis (H0) is rejected if the chi-square calculated value is greater than the chi-square critical value. Here x2 (calculated)>x2 (tabulated) or 18.65>9.48. Hence, we reject the null hypothesis and accept the alternative hypothesis. The p-valueP-valueP-Value, or Probability Value, is the deciding factor on the null hypothesis for the probability of an assumed result to be true, being accepted or rejected, & acceptance of an alternative result in case of the assumed results rejection. read more can also determine whether the null hypothesis must be accepted or rejected. For this, the p-value is compared with alpha (α) in the following way:
In this example, p-value<α or 0.0009172<0.05. So, we reject H0 and accept H1. We conclude that the quality of service is dependent on the salary of customers waiting to be served. Frequently Asked QuestionsHow should the chi-square test be interpreted? The “x2” in the goodness of fit test determines how well the sample data matches the characteristics of the larger population. If the sample data does not match the expected properties of the population, this sample is not used for drawing conclusions related to the larger population. What is the p-value in a chi-square test? The p-value, calculated in a chi-square test, represents an area in the tail of a probability distribution curve. A p-value is a number between zero and one. It is expressed in decimals. Which chi-square test should be used in Excel? The chi-square statistic to be used depends on how the data has been collected and which hypothesis is being tested. Key Takeaways
Recommended ArticlesThis has been a guide to Chi-Square Test in Excel. Here we learn how to perform the chi-square test along with step by step examples. Below are some useful Excel articles- |