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
actual_range: The counts that were observed.expected_range: The counts expected if the rows and columns were unrelated, the same size.
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
Do two groups answer differently?
A B C D E 1 Yes No 2 Group A 30 20 0.0455 3 Group B 20 30 4 Expected 25 25 5 Expected 25 25 In E2:
=CHISQ.TEST(B2:C3, B4:C5)→ 0.0455Just under 0.05: the groups probably do differ.
Are three options chosen equally often?
A B C D E F 1 A B C 2 Chosen 18 22 20 0.8187 3 Expected 20 20 20 In F2:
=CHISQ.TEST(B2:D2, B3:D3)→ 0.8187Nowhere near 0.05: nothing here suggests a preference.
Common mistakes
- Percentages instead of counts. The test needs the actual counts. With percentages the p-value is meaningless.
- Small expected counts. The test is unreliable when expected counts are below about 5. Combine categories or collect more data.
- Ranges of different sizes. The actual and expected ranges must be the same shape, or the result is #N/A.
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.
- Czech: CHISQ.TEST
- Danish: CHI2.TEST
- Dutch: CHIKW.TEST
- Finnish: CHINELIÖ.TESTI
- French: CHISQ.TEST
- German: CHIQU.TEST
- Hungarian: KHINÉGYZET.PRÓBA
- Italian: TEST.CHI.QUAD
- Norwegian: KJIKVADRAT.TEST
- Polish: CHI.TEST
- Portuguese (Brazil): TESTE.QUIQUA
- Portuguese (Portugal): TESTE.CHIQ
- Russian: ХИ2.ТЕСТ
- Spanish: PRUEBA.CHICUAD
- Swedish: CHI2.TEST
- Turkish: KİKARE.TEST
Also written as CHITEST.
Try CHISQ.TEST in the playground
Related functions
- CHISQ.DIST.RT: The right tail of the chi-square distribution: the chance of a value greater than x. This is the p-value of a chi-square statistic.
- CHISQ.INV.RT: The chi-square value with a given share of the distribution above it: the critical value of a chi-square test.
- T.TEST: Student's t-test: the chance that two samples would differ this much if they came from populations with the same mean.
- F.TEST: The F-test: the chance that two samples would have variances this different if their populations varied equally. Two-tailed.