Excel functions / Statistics

CHISQ.TEST function

The chi-square test of independence: the chance of counts this far from the expected ones if nothing but chance were at work.

Syntax

=CHISQ.TEST(actual_range, expected_range)

Arguments

When to use it

CHISQ.TEST compares counts you observed with the counts you would expect if nothing but chance were at work, and gives a p-value. A small p-value, by convention below 0.05, says the observed counts are too far from the expected ones to put down to chance.

It takes two ranges of the same size: the actual counts and the expected counts. For a table of rows and columns, each expected count is its row total times its column total, divided by the grand total.

Examples

  1. Do two groups answer differently?

    ABCDE
    1YesNo
    2Group A30200.0455
    3Group B2030
    4Expected2525
    5Expected2525

    In E2: =CHISQ.TEST(B2:C3, B4:C5) → 0.0455

    Just under 0.05: the groups probably do differ.

    Try this example

  2. Are three options chosen equally often?

    ABCDEF
    1ABC
    2Chosen1822200.8187
    3Expected202020

    In F2: =CHISQ.TEST(B2:D2, B3:D3) → 0.8187

    Nowhere near 0.05: nothing here suggests a preference.

    Try this example

Common mistakes

CHISQ.TEST in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Also written as CHITEST.

Try CHISQ.TEST in the playground

Related functions