The CORREL function

Returns the correlation coefficient of two ranges, from -1 to 1. Close to 1 means the two rise together, close to -1 that one falls as the other rises, and close to 0 no straight-line link.

How to use CORREL

CORREL tells you whether two columns of numbers move together, and how tightly. Think of ice cream sales and temperature: on hot days both go up. CORREL turns that feeling into a single number between -1 and 1.

Use it on pairs of numbers where row 2 in one column belongs with row 2 in the other: ad spend and sales per month, temperature and sales per day, hours of training and test score per employee. With =CORREL(A2:A6,B2:B6), a result near 1 means the two rise together, near -1 means one falls as the other rises, and near 0 means there is no straight-line link. The ranges must be the same size, and the order does not matter: swap them and you get the same number.

CORREL only finds straight lines. A curve or a single odd value can give a result near 0 even when the link is clear, so draw a scatter chart before you trust the number. A high result also says nothing about cause. To describe one column on its own, use AVERAGE or MEDIAN.

Syntax

CORREL(array1, array2)
array1
The first range of numbers.
array2
The second range, the same size as the first.

Examples

D1=CORREL(A2:A6,B2:B6)
ABCD
1Ad spendSales0.98644
210120
320150
430200
540210
650260

In plain words: How closely the ad spend in A2:A6 and the sales in B2:B6 move together.

Common CORREL mistakes

  • CORREL returns #N/A

    The two ranges have different sizes. Use the same number of cells in both, like =CORREL(A2:A13,B2:B13).

  • CORREL returns #DIV/0!

    One of the ranges is empty, has no numbers, or has the same value in every cell. CORREL needs numbers that vary in both columns.

  • The result is near 0 but the data clearly belong together

    CORREL measures straight-line links only. If the numbers follow a curve, or one extreme value pulls the result, plot the two columns in a scatter chart to see the real pattern.

  • The result ignores some of my numbers

    Numbers stored as text are skipped. They usually sit on the left side of the cell. Convert them to real numbers, for example with VALUE in a helper column, and run CORREL again.

Questions

Does a high correlation mean one thing causes the other?

No. CORREL only measures how closely two columns move together in a straight line. Both may be driven by something else, like the season.

What counts as a strong correlation?

A result above 0.7 or below -0.7 is often called strong, but it depends on your field and on how many data points you have. A few rows can give a high result by chance.

What is the difference between CORREL and PEARSON?

None. Both return the same correlation coefficient, so you can use either.

Related functions