The Pearson correlation coefficient (r) measures how closely two numbers move together in a straight line, from −1 (perfectly opposite) through 0 (no linear link) to +1 (perfectly together).
r = Σ (x − x̄)(y − ȳ) ÷ √( Σ (x − x̄)² × Σ (y − ȳ)² )
On the 30-day sample below: 0.72.
When to use it
It is the quickest way to see whether two things travel together, such as ad spend and orders, before deciding whether one might drive the other.
Worked example
Ad spend and website orders are positively correlated here, but day 17 had both the highest spend and the most orders, and that one day carries much of it: leave it out and r roughly halves. Always check a correlation with and without its most extreme points.
=CORREL(B2:B31,C2:C31)| Day | Ad spend (₹) | Website orders |
|---|---|---|
| 1 | ₹3,800 | 24 |
| 2 | ₹3,000 | 27 |
| 3 | ₹3,900 | 22 |
| 4 | ₹3,200 | 30 |
| 5 | ₹4,000 | 28 |
| 6 | ₹4,100 | 25 |
| 7 | ₹3,100 | 31 |
| 8 | ₹3,600 | 29 |
| 9 | ₹4,100 | 26 |
| 10 | ₹3,500 | 33 |
| 11 | ₹4,300 | 30 |
| 12 | ₹4,500 | 28 |
| 13 | ₹3,900 | 35 |
| 14 | ₹3,900 | 32 |
| 15 | ₹4,300 | 29 |
| 16 | ₹3,700 | 34 |
| 17 | ₹7,200 | 61 |
| 18 | ₹4,800 | 31 |
| 19 | ₹3,800 | 36 |
| 20 | ₹4,500 | 33 |
| 21 | ₹4,500 | 30 |
| 22 | ₹4,000 | 37 |
| 23 | ₹4,200 | 35 |
| 24 | ₹4,800 | 32 |
| 25 | ₹4,300 | 38 |
| 26 | ₹5,000 | 36 |
| 27 | ₹3,900 | 34 |
| 28 | ₹5,000 | 40 |
| 29 | ₹5,200 | 37 |
| 30 | ₹4,700 | 39 |
Common mistakes
- Correlation is not causation. Spend and orders can both rise because of a season or a sale.
- It only detects straight-line relationships; a curved relationship can have r near zero.
- Two trending series are correlated even when unrelated. Correlate the day-to-day changes instead when both grow over time.
Calculate it yourself
Related methods
See all statistics methods or the business metrics they are used on.
Guides that use it
- How to analyze your data without SQL or code — 2 min read
- Types of data analysis: descriptive, diagnostic, predictive and prescriptive — 2 min read
- Data visualization techniques: which chart for which question — 2 min read
- How to do data analysis: 6 steps with a worked example — 4 min read
- Data analysis methods and techniques, and when to use each — 3 min read
- Excel data analysis: formulas, PivotTables and the ToolPak — 3 min read
- Python and SQL for data analysis, side by side — 3 min read
- Data analysis examples: 6 worked from one dataset — 3 min read
Further reading
Questions people ask
- What is correlation coefficient?
- The Pearson correlation coefficient (r) measures how closely two numbers move together in a straight line, from −1 (perfectly opposite) through 0 (no linear link) to +1 (perfectly together).
- What is the formula for correlation coefficient?
- r = Σ (x − x̄)(y − ȳ) ÷ √( Σ (x − x̄)² × Σ (y − ȳ)² ). On the 30-day sample data the result is 0.72.
- How do you calculate correlation coefficient in Excel?
- Use =CORREL(B2:B31,C2:C31) with the data in rows 2 to 31. The same formula works in Google Sheets.
About the data on this page
The figures come from a small made-up dataset, printed above in full so you can check every number by hand. Download it as daily.csv. The results are computed from those rows when the page is built, and the Excel formulas, SQL and Python on these pages are run against the same rows before publishing, so the code and the answers agree.
To get these numbers from your own data without writing formulas, try AnalyzeIt: ask in plain words and it shows the working behind every figure.
Legal entity
Devansh Yadav
Moradabad, Uttar Pradesh
Governing law: Courts at Moradabad, Uttar Pradesh
Phone: +91 8433475698
Legal: legal@analyzeit.in