CHITEST function
CHITEST compares observed counts with expected counts and returns the probability that differences this large are just chance. It is a compatibility function; use CHISQ.TEST in new workbooks.
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
CHITEST(actual_range, expected_range)
-
actual_range(required) - Observed counts.
-
expected_range(required) - Expected counts, same shape as the observed ones.
Returns: The p-value for a chi-square test of independence or goodness of fit.
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 |
|---|---|---|
=CHITEST({10,20;30,40},{15,15;35,35}) | 0.029096332 | 2x2 arrays |
=CHITEST(Data!G1:G5,Data!J1:J5) | 0.452992614 | 1-row ranges |
=CHITEST(Data!B1:B4,Data!F1:F4) | 1.87293E-17 | Text in actual |
=CHITEST({1,2},{0,3}) | #DIV/0! | Expected contains zero |
=CHITEST(5,4) | #N/A | Single cell |
=CHITEST({1,2},{-1,3}) | #NUM! | Negative expected |
=CHITEST(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
- Superseded by CHISQ.TEST; use that in new workbooks.
- You must work out the expected counts yourself; the function does not do it.
- Both ranges must be the same size.
Where it works
- Excel
- CHITEST is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .