Excel functions / Statistics

CORREL function

The correlation coefficient of two data sets: from −1 (opposite) through 0 (unrelated) to 1 (move together).

Syntax

=CORREL(array1, array2)

Arguments

When to use it

CORREL measures how closely two columns of numbers move together, as one number from −1 to 1. Near 1, they rise together; near −1, one rises as the other falls; near 0, there's no straight-line relationship.

It answers "are these related, and how strongly?", which is the first question to ask before fitting a line with SLOPE or LINEST. Excel's Analysis ToolPak has a Correlation tool that does the same for every pair of columns, but CORREL updates when the data change.

Examples

  1. Temperature and sales

    ABCDE
    1TempConesRain
    21413260.98
    3191760
    4222401
    5111089
    6262710
    7161712

    In E2: =CORREL(A2:A7, B2:B7) → 0.98

    Try this example

  2. A negative relationship

    ABCDE
    1TempConesRain
    2141326-0.83
    3191760
    4222401
    5111089
    6262710
    7161712

    In E2: =CORREL(C2:C7, B2:B7) → -0.83

    More rain, fewer cones.

    Try this example

  3. Strength, ignoring direction

    ABCDE
    1TempConesRain
    2141326Strong
    3191760
    4222401
    5111089
    6262710
    7161712

    In E2: =IF(ABS(CORREL(C2:C7, B2:B7))>=0.7, "Strong", "Weak") → Strong

    Try this example

Common mistakes

Square the result (or use RSQ) to get the share of the variation one column explains in the other. Text and blank cells are skipped, along with the value they are paired with.

CORREL in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try CORREL in the playground

Practise it

Related functions