Correlation: do two things move together?
Measure how strongly two columns are related with CORREL, turn it into R², and label the strength. Intermediate · 5 min · CORREL, RSQ, Correlation, Analysis ToolPak
An ice cream kiosk wants to know what drives sales. Correlation measures how closely two sets of numbers move together, as a single number from −1 to 1:
- Close to 1: when one goes up, so does the other.
- Close to −1: when one goes up, the other goes down.
- Close to 0: no straight-line relationship.
=CORREL(array1, array2) works it out from two ranges of the same size. Excel's Data → Data Analysis → Correlation tool does the same for every pair of columns at once, but its table doesn't update when the data change. The formula does.
> Correlation is not causation. Hot days sell more ice cream, but selling ice cream doesn't make the day hot.
Steps
In
G2, measure how closely temperature and sales move together:=CORREL(B2:B13, D2:D13)Now rain. In
G3, find the correlation between rainfall in column C and cones sold in column D.R² is the correlation squared: the share of the ups and downs in sales that temperature explains. In
G4, use=RSQ(D2:D13, B2:B13), or simply squareG2.Label the strength in
G5. A common rule of thumb: 0.7 or more (ignoring the sign) is Strong, 0.4 or more is Moderate, anything less is Weak. UseABSso a strong negative correlation counts too:=IF(ABS(G2)>=0.7, "Strong", IF(ABS(G2)>=0.4, "Moderate", "Weak"))
Remember
=CORREL(range1, range2)gives r, from −1 to 1. The sign is the direction and the size is the strength.=RSQ(y, x)(or r squared) is the share of the variation in y that x explains.- Use
ABSwhen you only care how strong a relationship is, not which way it goes. - Can't see Data Analysis on the Data tab? It's an add-in that ships with Excel but starts switched off. On Windows: File → Options → Add-ins, choose Excel Add-ins next to Manage, click Go, tick Analysis ToolPak and click OK. On a Mac: Tools → Excel Add-ins, then tick Analysis ToolPak.
In the function reference: ABS, CORREL, IF, RSQ
Previous: Standard deviation and z-scores · Next: Linear regression: SLOPE, INTERCEPT and LINEST