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?