Browse

PEARSON Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

The PEARSON function in LibreOffice Calc returns the Pearson correlation coefficient between two datasets. This guide explains syntax, interpretation, examples, errors, and best practices.

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.

This guide provides syntax, interpretation rules, examples, common errors, and best practices, making it one of the most complete PEARSON references available for LibreOffice Calc.

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.