Google Sheets CHISQ.TEST Function: Syntax, Examples & Tips

What Is CHISQ.TEST?

CHISQ.TEST runs a Pearson chi-square test in a single formula. You supply the counts you observed and the counts you expected, and it returns the p-value: the probability of seeing differences at least this large if the null hypothesis were true. A small p-value (commonly below 0.05) suggests the observed pattern is unlikely to be due to chance alone.

CHISQ.TEST is the modern name for CHITEST; both behave the same way.

How CHISQ.TEST Works

You give CHISQ.TEST two ranges of the same size:

• observed_range: the counts you actually recorded
• expected_range: the counts you would expect if the null hypothesis were true

CHISQ.TEST calculates the chi-square statistic, the sum of (observed − expected)² ÷ expected over every cell, and converts it to a right-tailed p-value. Degrees of freedom are worked out from the shape of the ranges:

• A single row or single column of n cells uses n − 1 degrees of freedom (goodness-of-fit).
• A table with r rows and c columns, where both are greater than 1, uses (r − 1) × (c − 1) (test of independence).

The result is the p-value only. CHISQ.TEST does not return the statistic itself.

Basic Examples

Goodness-of-fit

Observed counts of 30 and 20 in B2:B3, with expected counts of 25 and 25 in C2:C3:

=CHISQ.TEST(B2:B3, C2:C3)

The statistic is (5² ÷ 25) + (5² ÷ 25) = 2 with 1 degree of freedom, so the result is about 0.157. A 60/40 split in 50 trials is not strong evidence against an even split.

Test of independence in a table

With observed counts in B2:D3 (2 rows × 3 columns), build the expected table in G2:I3 using row and column totals:

=SUM($B2:$D2) * SUM(B$2:B$3) / SUM($B$2:$D$3)

Fill that formula across G2:I3, then:

=CHISQ.TEST(B2:D3, G2:I3)

This returns the p-value with (2 − 1) × (3 − 1) = 2 degrees of freedom.

Even distribution across categories

=CHISQ.TEST(B2:B8, ARRAYFORMULA(B2:B8*0 + SUM(B2:B8)/7))

Tests whether counts for the seven days of the week are spread evenly, generating the expected counts inline.

Labelling the result

=IF(CHISQ.TEST(B2:D3, G2:I3) < 0.05, "Related", "No evidence of a relationship")

Getting the Statistic Too

If you also want to report the chi-square statistic, calculate it alongside the p-value:

=SUMPRODUCT((B2:D3-G2:I3)^2/G2:I3)

Feed it to CHISQ.DIST.RT with the degrees of freedom and you will get the same p-value that CHISQ.TEST returns.

Common Use Cases

• A/B testing: are conversion counts independent of the variant shown?
• Survey analysis: does preference differ by region, age group, or plan?
• Quality: are defects spread evenly across shifts or machines?
• Marketing: does channel mix differ between new and returning customers?

Common Errors

• #N/A or an error from mismatched ranges: The observed and expected ranges must be the same size and shape.
• Expected counts of zero: Division by zero makes the test impossible. Combine sparse categories.
• Passing percentages instead of counts: Both ranges should be counts. Expected values in proportions understate the statistic.
• Expected totals not matching: Expected counts should add up to the same total as the observed counts.
• Small samples: When many expected counts are below about 5, the chi-square approximation is unreliable.

Tips and Best Practices

• Build expected counts with formulas rather than typing them, so they update with the data.
• Report the p-value together with the statistic and degrees of freedom.
• A small p-value says the variables are related, not how strongly. Look at the table itself to see where the differences are.

Related Functions

• CHITEST: legacy chi-square test
• CHISQ.DIST.RT: right-tailed probability (p-value)
• CHISQ.INV.RT: critical value from a right-tail probability
• CHISQ.DIST: left-tailed probability or density
• IF: turn a p-value into a decision