RSQ Function

Returns R², the share of variation explained by a straight-line fit.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number from 0 to 1

Taught hands-on in Data Analytics · Relationships & Trends module

Overview

RSQ returns the square of the Pearson correlation. Read it as "the share of the ups and downs in y that the straight line through x explains": 0.85 means 85%.

It's the R² Excel shows on a chart trendline. Note the order: known y's first, then known x's (for RSQ itself the order doesn't change the answer).

Syntax

=RSQ(known_y's, known_x's) Explain

Arguments

ArgumentRequired?What it does
known_y'sYesThe values you want to explain (for example, sales).
known_x'sYesThe values that explain them (for example, ad spend), the same size.

Examples

Example 1 — How much does ad spend explain?

Sales in C, ad spend in B.

E2fx=RSQ(C2:C9,B2:B9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221R²0.9850
3Feb325
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=RSQ(C2:C9,B2:B9) Explain

About 98.5% of the month-to-month variation in sales lines up with ad spend.

Common mistakes

  • High R² isn't proof. Like correlation, a high R² doesn't show cause and effect — and with few points it can be high by chance.
  • #DIV/0! One range has no spread, or there are fewer than two pairs.
  • #N/A The ranges are different sizes.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.