Course / Data Analysis

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:

=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

  1. In G2, measure how closely temperature and sales move together: =CORREL(B2:B13, D2:D13)

  2. Now rain. In G3, find the correlation between rainfall in column C and cones sold in column D.

  3. 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 square G2.

  4. 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. Use ABS so a strong negative correlation counts too: =IF(ABS(G2)>=0.7, "Strong", IF(ABS(G2)>=0.4, "Moderate", "Weak"))

Remember

In the function reference: ABS, CORREL, IF, RSQ

Previous: Standard deviation and z-scores · Next: Linear regression: SLOPE, INTERCEPT and LINEST