R-squared from a trendline

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?

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

"r505el" wrote:

> 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?

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


"Jerry W. Lewis" wrote:

> 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
>
> "r505el" wrote:
>
> > 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?

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/...ATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/...ATA/NoInt2.dat

Jerry

"r505el" wrote:

> 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
>
>
> "Jerry W. Lewis" wrote:
>
> > 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
> >
> > "r505el" wrote:
> >
> > > 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?

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


"Jerry W. Lewis" wrote:

> 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/...ATA/NoInt1.dat
> http://www.itl.nist.gov/div898/strd/...ATA/NoInt2.dat
>
> Jerry
>
> "r505el" wrote:
>
> > 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
> >
> >
> > "Jerry W. Lewis" wrote:
> >
> > > 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
> > >
> > > "r505el" wrote:
> > >
> > > > 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?