CORREL Function (LibreOffice Calc)
The CORREL function in LibreOffice Calc returns the Pearson correlation coefficient between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the CORREL Function Does â–¾
- Calculates the Pearson correlation coefficient (r)
- Measures linear relationship strength and direction
- Works with numeric X/Y pairs
- Useful for statistics, forecasting, and exploratory analysis
- Works across sheets
The result is always between -1 and 1.
Syntax â–¾
CORREL(array1; array2)
Where:
array1— first dataset (X values)array2— second dataset (Y values)
Both arrays must be the same size.
Interpretation of CORREL Results â–¾
| Value | Meaning |
|---|---|
| 1 | Perfect positive linear correlation |
| 0 | No linear correlation |
| -1 | Perfect negative linear correlation |
| 0.7 to 1 | Strong positive |
| 0.3 to 0.7 | Moderate positive |
| 0 to 0.3 | Weak positive |
| -0.3 to 0 | Weak negative |
| -0.7 to -0.3 | Moderate negative |
| -1 to -0.7 | Strong negative |
Basic Examples â–¾
Correlation between two datasets
=CORREL(A1:A10; B1:B10)
Correlation across sheets
=CORREL(Sheet1.A1:A50; Sheet2.B1:B50)
Correlation using named ranges
=CORREL(Height; Weight)
Correlation with dates as X-values
=CORREL(A1:A100; B1:B100)
(Calc converts dates to serial numbers.)
Advanced Examples â–¾
Correlation ignoring errors
=CORREL(IF(ISNUMBER(A1:A100); A1:A100); IF(ISNUMBER(B1:B100); B1:B100))
(Confirm with Ctrl+Shift+Enter in older Calc.)
Correlation using filtered (visible) data only
Use SUBTOTAL helper column to filter X/Y before passing to CORREL.
Correlation after removing outliers
=CORREL(FILTER(A1:A100; A1:A100<1000); FILTER(B1:B100; A1:A100<1000))
Correlation for time-series analysis
=CORREL(Sales; MarketingSpend)
Correlation for normalized data
=CORREL((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Correlation for log-transformed data
=CORREL(LN(A1:A10); LN(B1:B10))
How CORREL Calculates the Pearson Coefficient â–¾
The formula is:
[ r = \frac{\sum (x_i - \bar{x})(y_i - \bar{y})}{\sqrt{\sum (x_i - \bar{x})^2 \cdot \sum (y_i - \bar{y})^2}} ]
Where:
- ( \bar{x} ) = mean of X
- ( \bar{y} ) = mean of Y
This is the same correlation used by PEARSON and LINEST.
Common Errors and Fixes â–¾
Err:502 — Invalid argument
Occurs when:
- Arrays have different sizes
- One or both arrays contain no numeric values
- Arrays contain only one data point
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
CORREL returns unexpected value
Possible causes:
- Relationship is non-linear
- Outliers distort correlation
- X-values or Y-values contain hidden text
- Data contains zeros that should be excluded
CORREL differs from PEARSON
They are identical — PEARSON is simply an alias.
Best Practices â–¾
- Use CORREL to measure linear relationships
- Plot data to confirm linearity before interpreting r
- Remove outliers when appropriate
- Use named ranges for cleaner formulas
- Use COVAR or COVARIANCE.P/S for deeper statistical modeling
- Use LINEST when you need full regression diagnostics
CORREL is the fastest way to understand whether two variables move together — essential for forecasting, analytics, and data-driven decision-making.