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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
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)