The CHITEST function in Google Sheets is a statistical tool used to evaluate the goodness-of-fit of observed categorical data against an expected distribution. By applying Pearson’s chi-squared test, it reveals the likelihood that any discrepancies between the observed and expected frequencies are due to random chance rather than a significant difference. This function is essential for researchers and analysts in fields such as social sciences, marketing, and quality control, where understanding categorical data relationships is crucial.

Syntax

CHITEST(observed_range, expected_range)

• observed_range: The range of cells containing the observed data counts.
• expected_range: The range of cells containing the expected data counts.

Example #1

CHITEST(A1:A10, B1:B10)

In this example, the function performs a chi-squared test comparing the observed values in cells A1 through A10 with the expected values in cells B1 through B10. A result of 0.045 suggests a 4.5% probability that differences between these data sets are due to chance.

Example #2

CHITEST(C1:C5, D1:D5)

This usage compares observed values in C1 to C5 against expected values in D1 to D5. If the output is 0.002, it indicates a 0.2% chance that the data differences occurred randomly, suggesting a statistically significant difference.

Example #3

CHITEST(E1:E10, F1:F10)

Here, observed data from E1 to E10 is analyzed against expected data from F1 to F10. A result of 0.150 shows a 15% likelihood that the divergences between observed and expected data are incidental, implying no significant difference.

Error handling

• N/A: This error arises if the ranges provided do not contain enough data to perform the test (e.g., only one value in either range).
• VALUE: This indicates that the inputs are of incorrect types, such as including text in a data range instead of numbers.
• REF: This error appears if the specified range is invalid, such as referencing a deleted range or an invalid cell address.

Conclusion

The CHITEST function is a valuable asset for anyone working with categorical data, offering insights into whether observed frequencies can be statistically attributed to population distributions. By understanding and applying this test, users can make informed decisions based on their data analysis, enhancing the reliability of their conclusions in various applications.