fx
FunctionPedia
Quick Answer

CORREL Function

Pearson correlation coefficient between two arrays.

✓ Excel✓ Google SheetsExcel All versions

Syntax

CORREL
(array1, array2)

Parameters

ParameterDescriptionRequired
array1The first set of independent (or paired) numeric values.Required
array2The second set of numeric values, the same length as array1.Required

Basic Example

Correlation between two paired series

=CORREL({2,3,5,7,9},{1,2,3,4,5})
Result0.993883

The two series move closely together; the coefficient is near 1, indicating a strong positive linear relationship.

Advanced Examples

Example 1: Correlation from ranges

Comparing advertising spend to sales

Both ranges must have the same number of points

=CORREL(B2:B13,C2:C13)
Result: Correlation coefficient between -1 and 1
Excel pairs B2 with C2, B3 with C3, and so on, so mismatched sizes cause #N/A.

Example 2: Negative relationship

Price versus demand

Higher price tends to lower demand

=CORREL(A2:A20,B2:B20)
Result: A negative value near -1
A coefficient close to -1 means the two variables move in opposite directions linearly.

How CORREL Works

CORREL computes r = Σ((x - x̄)(y - ȳ)) / sqrt(Σ(x - x̄)² · Σ(y - ȳ)²), where x̄ and ȳ are the means. The result always lies between -1 and 1: 1 is a perfect positive linear relationship, -1 a perfect negative one, and 0 no linear relationship. Text, logicals, and empty cells are ignored in each array.

1
Arrange paired data
Put the two related series in equal-length ranges.
2
Enter formula
Type =CORREL(array1, array2).
3
Read the coefficient
Values near 1 or -1 indicate strong linear association.

Important Notes & Limitations

  • Measures only linear association; it can be near 0 even when a strong non-linear relationship exists.

  • Both arrays must contain the same number of data points, or #N/A is returned.

  • If either array has zero variance (all identical values), the function returns #DIV/0!.

Common Errors & Fixes

#N/AThe two arrays have different numbers of data points.

Fix: Make sure array1 and array2 are the same length.

#DIV/0!One of the arrays has no variance (all values equal).

Fix: Use data with variation, or check for constant columns.

Download Practice File

Practice CORREL with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the CORREL function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Is CORREL the same as PEARSON?
Yes. In both Excel and Google Sheets, CORREL and PEARSON return the identical Pearson coefficient.
What does a correlation of 0 mean?
It means there is no linear relationship; a non-linear relationship may still exist.
Does correlation imply causation?
No. Correlation only measures association, not cause and effect.