CHISQ.TEST Function

Returns the p-value of a chi-square test comparing observed and expected counts.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice CHISQ.TEST in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

CHISQ.TEST checks whether counts differ from what you expected more than chance would explain — tickets by weekday, sales by region, answers to a survey. It returns the p-value. A p-value is the chance of seeing a difference at least this big if there were really no difference. Below 0.05 is the usual cut-off for calling a result statistically significant.

You supply the expected counts yourself. For a table testing whether two categories are related, the expected count for each cell is row total × column total ÷ grand total.

Syntax

=CHISQ.TEST(actual_range, expected_range) Explain

Arguments

ArgumentRequired?What it does
actual_rangeYesThe observed counts.
expected_rangeYesThe expected counts, the same shape.

Examples

Example 1 — Are tickets spread evenly across the week?

Observed tickets per weekday against an even 24 a day.

E2fx=CHISQ.TEST(B2:B6,C2:C6)
ABCDE
1DayTicketsExpectedMeasureResult
2Mon2824p-value0.4409
3Tue2224
4Wed1924
5Thu2124
6Fri3024
=CHISQ.TEST(B2:B6,C2:C6) Explain

p ≈ 0.44 — no real evidence that some days are busier than others.

Common mistakes

  • Counts only. Use raw counts, not percentages or averages.
  • Small expected counts. The test is unreliable when expected counts are below about 5.
  • #N/A The two ranges hold different numbers of values.

Notes

  • Degrees of freedom are worked out automatically: count − 1 for a single row or column, (rows − 1) × (columns − 1) for a table.
  • CHISQ.TEST replaces the older CHITEST function.
  • Related functions: CHISQ.DIST.RT, CHISQ.INV.RT.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.