The CHISQ.TEST is a Statistical function that calculates the chi squared (χ2) distribution and returns the probability of a value chi-squared (χ2) between observed and expected data. The chi squared (χ2) test can be used to assess the relationship between two data sets. In this guide, we’re going to show you how to use the CHISQ.TEST function and also go over some tips and error handling methods. CHISQTET
Supported versions
- Excel 2010 and newer
Syntax of CHIS TE
Arguments
| actual_range | An array of data containing observations that are to be tested against expected values. |
| expected_range | An array of data containing the ratio of the product of row totals and column totals to the grand total. |
Example of CHISQ TEST
Let’s assume we have 4 x 3 table.
You can calculate the theoretical values by multiplying each row by each column, and dividing the number by the sum of all data. You can either use a helper row and column - like in our example - or use the SUM function instead. Remember that the actual data and expected data should be the same size.
Once the expected values (expected_range) ready, you can use the CHISQ.TEST function with actual_range and expected_range respectively.
The CHISQ.TEST function returns the probability of whether the value of the chi-square distribution is likely to happen. Generally speaking, a probability of 0.05 or less is significant. Therefore, the returned value of 0.0014 in our example indicates a significant difference.
Tips
- Each actual_range and expected_range must be the same size.
- The CHISQ.TEST uses the χ2 distribution with an appropriate number of degrees of freedom, df. If r > 1 and c > 1, then df = (r - 1)(c - 1). If r = 1 and c > 1, then df = c - 1 or if r > 1 and c = 1, then df = r - 1.
Issues
- If actual_range and expected_range have a different number of data points, the CHISQ TEST function returns the #N/A error value.
If row or column number is 1 (i.e. not a table), the function returns #N/A error value.
