CHISQ.TEST Function (LibreOffice Calc)
The CHISQ.TEST function in LibreOffice Calc performs a chi-square test for independence using observed and expected frequencies. This guide explains syntax, interpretation, examples, errors, and best practices.
This guide provides syntax, interpretation rules, examples, common errors, and best practices, making it one of the most complete CHISQ.TEST references available for LibreOffice Calc.
What the CHISQ.TEST Function Does
- Tests whether two categorical variables are independent
- Compares observed vs expected frequencies
- Returns a p-value from the chi-square distribution
- Works with 2×2 or larger contingency tables
- Essential for surveys, experiments, and categorical data analysis
CHISQ.TEST answers:
“If the variables were truly independent, what is the probability of observing a table this different from expectation?”
Syntax
CHISQ.TEST(actual_range; expected_range)
Where:
actual_range— observed frequenciesexpected_range— expected frequencies (same dimensions)
Structure of a Contingency Table
Example 2×2 table:
| Yes | No | Total | |
|---|---|---|---|
| Group A | 30 | 20 | 50 |
| Group B | 10 | 40 | 50 |
| Total | 40 | 60 | 100 |
Expected frequency formula:
[ E_{ij} = \frac{(\text{row total})(\text{column total})}{\text{grand total}} ]
Basic Examples
Chi-square test for a 2×2 table
Observed:
A1:B2
Expected:
D1:E2
Formula:
=CHISQ.TEST(A1:B2; D1:E2)
Chi-square test for larger tables (e.g., 3×3)
=CHISQ.TEST(A1:C3; E1:G3)
Across sheets
=CHISQ.TEST(Sheet1.A1:B2; Sheet2.A1:B2)
Using named ranges
=CHISQ.TEST(Observed; Expected)
Advanced Examples
Automatically computing expected frequencies
Expected cell:
=SUM(A1:A2) * SUM(A1:B1) / SUM(A1:B2)
Then fill across the table.
Chi-square test ignoring errors
=CHISQ.TEST(IF(ISNUMBER(A1:B2); A1:B2); IF(ISNUMBER(D1:E2); D1:E2))
(Confirm with Ctrl+Shift+Enter in older Calc.)
Chi-square test after filtering data
Use COUNTIFS to build observed frequencies from filtered data.
Chi-square test for survey data
=CHISQ.TEST(ResponsesTable; ExpectedTable)
Chi-square test for independence in experiments
=CHISQ.TEST(ObservedOutcomes; ExpectedOutcomes)
Chi-square test for goodness-of-fit (manual)
Goodness-of-fit uses:
=CHISQ.TEST(Observed; Expected)
Even though it’s not a contingency table.
How CHISQ.TEST Calculates the p-value
- Compute chi-square statistic:
[ \chi^2 = \sum \frac{(O - E)^2}{E} ]
- Compute degrees of freedom:
[ df = (r - 1)(c - 1) ]
- Compute p-value:
[ p = 1 - F_{\chi^2}(\chi^2, df) ]
Where ( F_{\chi^2} ) is the chi-square CDF.
Interpretation of CHISQ.TEST Results
| p-value | Meaning |
|---|---|
| < 0.05 | Variables are likely dependent |
| > 0.05 | No evidence of dependence |
| Near 1 | Observed table very close to expected |
Common Errors and Fixes
Err:502 — Invalid argument
Occurs when:
- Actual and expected ranges differ in size
- Expected frequencies contain zeros
- Expected frequencies not computed correctly
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
CHISQ.TEST returns unexpected value
Possible causes:
- Expected frequencies too small (< 5)
- Sparse data
- Incorrect expected table
- Non-independent categories
Best Practices
- Ensure expected frequencies are ≥ 5 for reliable results
- Use COUNTIFS to build observed tables from raw data
- Use named ranges for cleaner formulas
- Use CHISQ.INV and CHISQ.DIST for manual chi-square calculations
- Avoid chi-square tests on very small samples
- Use Fisher’s exact test for 2×2 tables with small counts (not built-in)