Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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.

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 .