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
array1: The first set of values.array2: The second set, the same size as the first.
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
Temperature and sales
A B C D E 1 Temp Cones Rain 2 14 132 6 0.98 3 19 176 0 4 22 240 1 5 11 108 9 6 26 271 0 7 16 171 2 In E2:
=CORREL(A2:A7, B2:B7)→ 0.98A negative relationship
A B C D E 1 Temp Cones Rain 2 14 132 6 -0.83 3 19 176 0 4 22 240 1 5 11 108 9 6 26 271 0 7 16 171 2 In E2:
=CORREL(C2:C7, B2:B7)→ -0.83More rain, fewer cones.
Strength, ignoring direction
A B C D E 1 Temp Cones Rain 2 14 132 6 Strong 3 19 176 0 4 22 240 1 5 11 108 9 6 26 271 0 7 16 171 2 In E2:
=IF(ABS(CORREL(C2:C7, B2:B7))>=0.7, "Strong", "Weak")→ Strong
Common mistakes
- Reading it as cause and effect. A strong correlation says two things move together, not that one causes the other. Hot days sell more ice cream; the ice cream does not heat the day.
- Ranges of different sizes. Both ranges need the same number of cells, paired row by row. Otherwise CORREL returns #N/A.
- Expecting it to spot curves. CORREL measures straight-line relationships. Data that rise and then fall can show a correlation near 0 and still be closely related.
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.
- Czech: CORREL
- Danish: KORRELATION
- Dutch: CORRELATIE
- Finnish: KORRELAATIO
- French: COEFFICIENT.CORRELATION
- German: KORREL
- Hungarian: KORREL
- Italian: CORRELAZIONE
- Norwegian: KORRELASJON
- Polish: WSP.KORELACJI
- Portuguese (Brazil): CORREL
- Portuguese (Portugal): CORREL
- Russian: КОРРЕЛ
- Spanish: COEF.DE.CORREL
- Swedish: KORREL
- Turkish: KORELASYON
Practise it
Related functions
- RSQ: R squared: the share of the variation in y that the best-fit line explains, from 0 to 1.
- PEARSON: The Pearson correlation coefficient, the same as CORREL.
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- COVARIANCE.S: Sample covariance: like COVARIANCE.P, but divides by n − 1 for a sample.