Excel functions / Statistics
RSQ function
R squared: the share of the variation in y that the best-fit line explains, from 0 to 1.
Syntax
=RSQ(known_y, known_x)
Arguments
known_y: The dependent values: what you want to explain or predict.known_x: The independent values, one for each known_y.
When to use it
RSQ gives R squared: the share of the variation in y that the best-fit straight line through x explains, from 0 to 1. An RSQ of 0.9 means the line accounts for 90% of the ups and downs; 0.1 means it explains very little.
It is the correlation (CORREL) squared, and it is the "R Square" line in the Analysis ToolPak's Regression report.
Examples
How well ad spend explains sales
A B C D 1 Ad spend Sales 2 10 120 0.988 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=RSQ(B2:B6, A2:A6)→ 0.988As a percentage
A B C D E 1 Temp Cones Rain 2 14 132 6 97% 3 19 176 0 4 22 240 1 5 11 108 9 6 26 271 0 7 16 171 2 In E2:
=RSQ(B2:B7, A2:A7)→ 97%Temperature explains about 97% of the change in cones sold.
Common mistakes
- Treating a high RSQ as proof. A high R² shows a strong straight-line pattern in this data, not that x causes y or that the pattern will continue.
- Comparing it across very different data. Noisy everyday data rarely reach the R² of a lab experiment. Judge it against what is typical for the kind of data you have.
RSQ 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: RKQ
- Danish: FORKLARINGSGRAD
- Dutch: R.KWADRAAT
- Finnish: PEARSON.NELIÖ
- French: COEFFICIENT.DETERMINATION
- German: BESTIMMTHEITSMASS
- Hungarian: RNÉGYZET
- Italian: RQ
- Norwegian: RKVADRAT
- Polish: R.KWADRAT
- Portuguese (Brazil): RQUAD
- Portuguese (Portugal): RQUAD
- Russian: КВПИРСОН
- Spanish: COEFICIENTE.R2
- Swedish: RKV
- Turkish: RKARE
Practise it
- Correlation: do two things move together?
- Linear regression: SLOPE, INTERCEPT and LINEST
- Trendlines and forecasts
Related functions
- CORREL: The correlation coefficient of two data sets: from −1 (opposite) through 0 (unrelated) to 1 (move together).
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- LINEST: Fits a straight line by least squares and returns its slope(s) and intercept, with the full regression statistics (standard errors, R², F, sums of squares) if you ask. Spills.
- STEYX: The standard error of the regression: the typical distance of a y value from the best-fit line.