When analysing survey demographics, conversion experiments, or medical trial distributions, researchers frequently need to determine whether two categorical variables are genuinely independent or if observed frequencies deviate significantly from expected baseline probabilities. In classical statistics, evaluating this relationship requires computing a Pearson’s chi-square test statistic (χ²) across contingency tables. Rather than manually calculating cell deviations and referencing statistical probability tables, Google Sheets provides a dedicated statistical function: CHISQ.TEST.
Understanding the Chi-Square Test of Independence
The chi-square test evaluates the null hypothesis ($H_0$) that categorical outcomes occur purely by chance and are statistically independent against the alternative hypothesis ($H_1$) that a significant association exists between the variables:
- Observed Range (Actual Data): The empirical frequency counts collected from your observational sample or experimental groups.
- Expected Range (Theoretical Data): The theoretical frequency counts anticipated if the two variables were completely independent.
- Returned Value:
CHISQ.TESTreturns the exact $p$-value associated with the test statistic and degrees of freedom directly, rather than the raw χ² test score. - Significance Threshold (α): If the returned $p$-value is less than or equal to your significance level (typically
0.05for a 95% confidence threshold), you reject the null hypothesis and conclude that the relationship is statistically significant.
| Metric | Role in Calculation | Formula / Mathematical Definition |
|---|---|---|
| Observed Frequencies ($O$) | Empirical sample counts | Continuous cell range (e.g. B2:C4) |
| Expected Frequencies ($E$) | Theoretical independent distribution | (Row Total * Column Total) / Grand Total |
| Degrees of Freedom ($df$) | Determines distribution curve shape | (Rows - 1) * (Columns - 1) |
| Output Result | Chi-square probability ($p$-value) | =CHISQ.TEST(observed_range, expected_range) |
Syntax and Parameter Requirements
The syntax for the function in Google Sheets requires two distinct cell ranges of identical dimensional shape:
=CHISQ.TEST(actual_range, expected_range)actual_range: The grid or vector containing your observed categorical counts. Every cell must contain a non-negative numerical integer or float.expected_range: The corresponding grid containing theoretical expected frequencies. Must have the exact same number of rows and columns as theactual_range.
Step-by-Step Example: Website Subscription Preference by Device
Consider an experiment evaluating whether user subscription choice (Free vs Premium) depends on device type (Desktop vs Mobile). Suppose you surveyed 300 users and tabulated the following Observed Frequencies in cells B2:C3:
| Device Type (Row) | Free Tier (Col B) | Premium Tier (Col C) | Row Total |
|---|---|---|---|
| Desktop | 60 | 90 | 150 |
| Mobile | 90 | 60 | 150 |
| Column Total | 150 | 150 | 300 (Grand Total) |
Calculating the Expected Frequencies Table
Under the null hypothesis of complete independence, expected cell values equal (Row Total * Column Total) / Grand Total:
- Desktop Free Tier:
(150 * 150) / 300 = 75 - Desktop Premium Tier:
(150 * 150) / 300 = 75 - Mobile Free Tier:
(150 * 150) / 300 = 75 - Mobile Premium Tier:
(150 * 150) / 300 = 75
Place these expected counts into a secondary range, such as E2:F3.
Executing the Formula and Interpreting Results
In an empty summary cell, enter the formula:
=CHISQ.TEST(B2:C3, E2:F3)Result: 0.00053 (approx. 0.053%).
Because 0.00053 is substantially lower than α = 0.05, you reject the null hypothesis. There is strong evidence that subscription tier selection is statistically correlated with device platform, indicating desktop users are significantly more likely to select the premium tier compared to mobile visitors.
Automating Hypothesis Testing Decisions
To eliminate manual interpretation across multiple testing matrices, combine CHISQ.TEST with an IF logical statement:
=IF(CHISQ.TEST(B2:C3, E2:F3) < 0.05, "Statistically Significant (Reject H0)", "Not Significant (Fail to Reject H0)")Troubleshooting Common CHISQ.TEST Errors
- #N/A Error (Range Mismatch): Occurs if
actual_rangeandexpected_rangehave unequal dimensions (for example, comparing a 2×3 observed matrix against a 2×2 expected table). Both ranges must match row-for-row and column-for-column. - #NUM! Error (Invalid Degrees of Freedom or Small Samples): Triggers if degrees of freedom is less than 1 ($df < 1$). In a 1×1 table, $df = (1-1)(1-1) = 0$, producing a mathematical error. Ensure tables have at least 2 rows or 2 columns.
- #DIV/0! Error: Occurs if all observed counts or expected totals sum to zero.
- Small Expected Counts Warning (Cochran’s Rule): If any expected frequency is zero, or if more than 20% of expected cells have counts less than 5, the chi-square approximation becomes unreliable. In such cases, combine sparse categories or consider Fisher’s exact test.