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.
Verification status: Two identified examples were executed in LibreOffice Calc 25.2. Other examples are not necessarily application-tested. How formula examples are checked
Tested visual example: Relationship between two measurements
What this example answers: Compare practice hours with test results using Pearson correlation. The formula was entered and recalculated in LibreOffice Calc 25.2.3.2, not simulated or inferred from a reference page.
Sample sheet you can reproduce
Create a sheet named Example, put these headings on row 4, and enter the sample values in rows 5–10. Or download the editable CORREL practice workbook with the formulas already entered.
| Sheet row | A: Practice hours | B: Test score | C: Participant | D: Group |
|---|---|---|---|---|
| 5 | 1 | 2 | A | Pilot |
| 6 | 2 | 4 | B | Pilot |
| 7 | 3 | 5 | C | Pilot |
| 8 | 4 | 4 | D | Pilot |
| 9 | 5 | 8 | E | Pilot |
| 10 | 6 | 9 | F | Pilot |
Example 1 — Formula and expected result
Select cell E5, enter the formula and press Enter:
=CORREL(A5:A10;B5:B10)
Verified result: 0.9249.
The coefficient measures strength and direction of the linear relationship; it does not establish causation.
Interpretation: A value near +1 indicates a strong positive linear association. It does not imply that practice hours caused the scores.
Genuine screenshot from LibreOffice Calc 25.2.3.2: the formula bar, input table and computed output are visible. No interface imagery was generated.
Example 2 — Change the inputs
The same downloadable .ods file has this second formula in E12:
=CORREL(A5:A8;B5:B8)
Verified result: 0.7182. Use only the first four paired observations. Correlation changes when the sample changes; it is not a fixed property of the columns.
How to avoid misleading results
- Watch for this mistake: Both ranges need the same number of observations and numeric variation; constant values make the correlation undefined.
- Scope and limitations: Correlation is sensitive to outliers. Inspect a scatter plot as well as the coefficient.
- Argument separators: These examples use semicolons (
;) in the LibreOffice Calc locale tested. Other locales may use different settings. - Verification scope: The two formulas in this section were executed and checked in LibreOffice Calc 25.2.3.2. Older examples elsewhere in this article have not all been independently retested.
Official function reference: LibreOffice Help — CORREL.
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)
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