R-squared from a trendline

[Guest]

What is the difference between (1) the R-squared value generated when you add
a linear trendline on a chart and (2) the R-squared value generated when you
do regression in the Data Analysis add-in?

 

[Guest]

Not enough information. What kind of chart?

If the chart is an "XY (Scatter)" chart, then mathematically there is no
difference between the R-squared calculated by linear chart trendline, the
ATP Regression tool, LINEST (fitting a straight line) or the RSQ worksheet
function. Numerically, there may be a difference in Excel versions prior to
2003 because the algorithms used by ATP, LINEST, and RSQ did not use the
available numeric accuracy as efficiently as did the chart trendline.

If the chart is a "Line" chart, then both the R-squared and the trendline
estimated by the chart assume that predictor (x) values are 1,2,3,... even if
you specified different values.

Jerry

 

[Guest]

Thanks for responding.

I plotted an XY scatter chart, setting the intercept to 0 and displaying the
R-squared value. I was surprised to see that the R-squared value I obtained
from SAS was different. So I went back to Excel, did "Regression" under Tools

Data Analysis, set the intercept to 0 also, and the R-squared value was the

same as the value I obtained from SAS.

I thought that maybe there was something wrong with my data, so I made up
some X and Y values and did the same comparison. And indeed, both methods in
Excel give different values.

r505el

 

[Guest]

You neglected to mention that you were forcing the intercept to zero. That
has been a known issue with Excel for years. LINEST in 2003 (& therefore
ATP) fixed R-square, but the chart platform did not. The ATP "Adjusted
R-square" is still wrong also.

NIST gives data and certified 15-figure results for this type of calculation
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt2.dat

Jerry

 

[Guest]

Okay, everything makes sense now. I wouldn't have known that the R-square on
the chart trendline was wrong (when intercept was forced to zero). But yes,
thank you very much.

r505el