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