PEARSON Function (LibreOffice Calc)
The PEARSON function in LibreOffice Calc returns the Pearson correlation coefficient between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: Documented by LibreOffice 26.8.1.1; original introduction not established.
- Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
The **PEARSON** function returns the **Pearson correlation coefficient (r)** between two datasets. It is mathematically identical to the CORREL function and measures the strength and direction of a linear relationship between two variables.
What the PEARSON Function Does
- Calculates the Pearson correlation coefficient
- 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
PEARSON(array1; array2)
Where:
array1— first dataset (X values)array2— second dataset (Y values)
PEARSON and CORREL return identical results.
Interpretation of Pearson Correlation
| 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
=PEARSON(A1:A10; B1:B10)
Correlation across sheets
=PEARSON(Sheet1.A1:A50; Sheet2.B1:B50)
Correlation using named ranges
=PEARSON(Height; Weight)
Correlation with dates as X-values
=PEARSON(A1:A100; B1:B100)
(Calc converts dates to serial numbers.)
Advanced Examples
Correlation ignoring errors
=PEARSON(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 PEARSON.
Correlation after removing outliers
=PEARSON(FILTER(A1:A100; A1:A100<1000); FILTER(B1:B100; A1:A100<1000))
Correlation for time-series analysis
=PEARSON(Sales; MarketingSpend)
Correlation for normalized data
=PEARSON((A1:A10 - AVERAGE(A1:A10)); (B1:B10 - AVERAGE(B1:B10)))
Correlation for log-transformed data
=PEARSON(LN(A1:A10); LN(B1:B10))
How PEARSON Calculates the Correlation 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 CORREL 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
PEARSON 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
PEARSON differs from CORREL
They are identical — PEARSON is simply an alternate name.
Best Practices
- Use PEARSON when you want explicit Pearson correlation
- Use CORREL for compatibility with older spreadsheets
- Use COVARIANCE.P/S for raw covariance
- Remove outliers when appropriate
- Use named ranges for cleaner formulas
- Use LINEST when you need full regression diagnostics
PEARSON gives you a clean, normalized measure of how two variables move together — essential for analytics, forecasting, and statistical modeling.