CHISQ.TEST function
CHISQ.TEST compares what you observed with what you expected and returns a p-value. A small value suggests the difference is unlikely to be chance, as when checking whether survey answers depend on a group.
Example
Read it as a sentence: compare the actual values in G1:G5 with the expected values in J1:J5 and give the chi-square test probability.
Excel shows 0.452992614
G
J
1
2
3
2
4
3
3
5
3
4
4
3
5
5
3
Data
Syntax
CHISQ.TEST(actual_range, expected_range)
-
actual_range(required) - Observed counts.
-
expected_range(required) - Expected counts, same shape as actual_range.
Returns: The p-value of a chi-squared test comparing observed with expected counts.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CHISQ.TEST({10,20;30,40},{15,15;35,35}) | 0.029096332 | 2x2 arrays |
=CHISQ.TEST(Data!G1:G5,Data!J1:J5) | 0.452992614 | 1-row ranges |
=CHISQ.TEST(Data!B1:B4,Data!F1:F4) | 1.87293E-17 | Text in actual |
=CHISQ.TEST({1,2},{0,3}) | #DIV/0! | Expected contains zero |
=CHISQ.TEST(5,4) | #N/A | Single cell |
=CHISQ.TEST({1,2},{-1,3}) | #NUM! | Negative expected |
=CHISQ.TEST(Data!G1:G5,Data!K1:K3) | #N/A | Different sizes |
The Data sheet these formulas read
A cell that starts with = holds that formula.
B
F
G
J
K
1
10
1
2
3
1
2
20
2
4
3
2
3
TRUE
3
5
3
3
4
4
4
3
5
abc
5
5
3
Data
Watch out for
- You must build the expected counts yourself.
- Both ranges need identical dimensions.
- Small expected counts make the test unreliable.
Where it works
- Excel
- CHISQ.TEST arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .