CHISQ.TEST in Google Sheets: Observed and Expected Counts

=CHISQ.TEST(observed_range, expected_range)

Google’s function catalogue directs CHISQ.TEST to the individual CHITEST reference. Both take observed category counts first and the null model’s expected counts second.

The two ranges must match in both row and column dimensions. Google also documents paired omission: if either cell of a corresponding pair is nonnumeric, that pair is excluded from the calculation. The arguments are required; there are no optional defaults or a user-specified degrees-of-freedom argument.

The output is a p-value, not the chi-square statistic. Keep a separate statistic calculation when you need to audit which categories drove the result.

Suppose a model specified equal probabilities for categories A, B and C before collecting 60 independent observations. Enter this complete table in A1:C4:

In F2 enter:

=CHISQ.TEST(B2:B4,C2:C4)

Expected mathematical p-value: 0.006737946999085467, roughly 0.67379%, for the standard three-category Pearson test with no fitted category probabilities. This expectation was calculated independently, not observed in a Sheets session.

Each expected count is 60×(1/3) = 20, and both columns total 60. Do not enter expected proportions of 1/3 in C2:C4 while leaving observed data as counts. That would mix incompatible scales.

Put Contribution in D1. In D2 enter and fill down to D4:

=(B2-C2)^2/C2

Expected D2:D4: 5, 0, 5. Then in F3 enter:

=SUM(D2:D4)

Expected statistic: 10. In this prespecified goodness-of-fit model, the standard degrees of freedom are three categories minus one, or 2. The corresponding right-tail calculation is:

=CHISQ.DIST.RT(F3,2)

Expected result: 0.006737947, independently equal to exp(−10/2) for two degrees of freedom. This makes the statistic, degree choice and tail explicit.

The NIST goodness-of-fit reference explains the sum of squared observed-minus-expected differences divided by expected counts. If model parameters were fitted from these same observations, the usual degrees of freedom need adjustment. CHISQ.TEST has no argument for that adjustment; calculate the statistic and use a suitable distribution function with the justified degrees instead.

Under the specified null model, a statistic at least this large has a chi-square-reference tail area of about 0.00674. That is evidence of a discrepancy with this model under the stated assumptions.

It does not mean there is a 0.674% probability that the model is true, nor a 99.326% probability of a particular alternative. The p-value also does not measure the practical importance of the difference. Report the category counts and differences alongside it.

An observed zero can be meaningful. An expected zero makes the usual Pearson contribution undefined; do not suppress that by substituting a small arbitrary number. Review the model or use a method appropriate to the structural-zero situation.

On a fresh sheet, put these observed counts in B2:D3:

Each row total is 60; each column total is 40; grand total is 120. Under independence, the expected count in every cell is 60×40/120 = 20. Put six numeric 20s in F2:H3.

=CHISQ.TEST(B2:D3,F2:H3)

The standard Pearson independence-test expectation is statistic 20, degrees of freedom (2−1)×(3−1) = 2, and p-value 0.00004539992976. The NIST contingency-table reference explains expected counts and the row/column degrees-of-freedom rule.

Do not include row totals, column totals or labels in either test range. Do not flatten the two-dimensional table into one column merely to make the shapes match: the table structure identifies the statistical question. The numeric result above is an independent standard-test expectation; Google’s short help does not enumerate every degenerate-shape or missing-pair degree rule.

• Mismatched dimensions: Correct both ranges so every observed category is paired with its expected count.
• Numbers stored as text: Google’s paired omission rule can silently remove information. Validate numeric counts before testing rather than treating a returned number as proof that all categories were used.
• Expected counts too small: The chi-square approximation may be inappropriate. Check the study design and an appropriate alternative; do not combine unrelated categories solely to force a result.
• Totals disagree: Verify that expected counts describe the same total as observed counts for the intended goodness-of-fit model.
• Data are percentages or raw observations: First construct the count table required by the test.

The cited Google help does not give a comprehensive error-token matrix. No exact native error code is asserted for zero expected counts, negative values, empty ranges or malformed shapes. Its example range/prose is not used as a validated native fixture here; the worked tables above have explicit numeric-only boundaries and independent calculations.

CHISQ.TEST is listed by Google as the name routed to CHITEST for the current rolling Sheets service. An original introduction date or last functioning numbered release was not established. Statistical degrees-of-freedom rules and continuity corrections must be checked when moving a test between applications; a matching name is insufficient evidence of every edge case.

Evidence status: NOT_TESTED_IN_GOOGLE_SHEETS. Google documentation and NIST references were inspected on October 17, 2026. The two demonstrations were calculated independently under their stated standard Pearson models. No native Sheets run, screenshot or account-specific verification was performed.

Related published guides: COUNTIF, COUNTIFS, SUM and SUMPRODUCT.