Function: CHISQ.TEST

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

CHISQ.TEST(actual_range, expected_range)

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.

Expected value(ij) row(i) * column(j) / total

Once the expected values (expected_range) ready, you can use the CHISQ.TEST function with actual_range and expected_range respectively.

=CHISQ.TEST(B7:D10,B16:D19)

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.