Quick Navigation
CHISQ.TEST Function
Summary
The CHISQ.TEST function performs a chi-squared test for independence, comparing observed categorical data against expected frequencies to determine if observed patterns occurred by chance or indicate true association.
Syntax
CHISQ.TEST(actual_range, expected_range)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| actual_range | Range |
Yes | The range of data that contains observations to test against expected values |
| expected_range | Range |
Yes | The range of data that contains the ratio of the product of row totals and column totals to the grand total |
Using the CHISQ.TEST Function
CHISQ.TEST is essential for categorical data analysis in Excel. Use it to test whether two categorical variables are independent by comparing observed frequencies against expected frequencies calculated under the null hypothesis of independence. The function returns a p-value indicating the probability of observing the data (or more extreme) assuming independence.
Common CHISQ.TEST Examples
Testing Gender Opinion Independence
=CHISQ.TEST(A2:B4,A6:B8)Tests if opinion on a topic (Agree/Neutral/Disagree) is independent of gender using observed counts in A2:B4 and expected frequencies in A6:B8. Returns p-value of 0.0003082 indicating strong evidence against independence.
Marketing Campaign Effectiveness
=CHISQ.TEST(B2:C5,B8:C11)Compares actual vs expected customer responses across channels to test if campaign distribution matches target proportions.
Frequently Asked Questions
Common Errors and Solutions
#N/A error
Cause: actual_range and expected_range have different number of data points
Solution: Ensure both ranges contain exactly the same number of cells
#VALUE! error
Cause: Ranges contain non-numeric values
Solution: Verify all cells in both ranges contain valid numbers
Unexpected high p-values
Cause: Expected frequencies too small (<5 recommended)
Solution: Combine categories or collect more data to ensure adequate expected frequencies
Notes
- Each expected frequency should ideally be ≥5 for test validity
- Low χ² values indicate data matches independence hypothesis
- Test assumes categorical data, not continuous measurements
- Degrees of freedom = (rows-1)×(columns-1) for RxC tables
- Available in Excel 2010 and later versions
Compatibility
Available in: Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 365
Not available in: Excel 2007 and earlier
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2010+, Excel 2013, Excel 2016, Excel 2019, Excel 365