Regression analysis excel 2002

Can anybody explain why I get different values for regression analysis using
the data analysis under "tools" and when the add trendline is used. Note
the intercept is not set to zero.

A B
2 3
3 4
5 7
7 11
9 13



















SUMMARY OUTPUT

Regression Statistics
Multiple R 0.994674781
R Square 0.989377919
Adjusted R Square 0.985837225
Standard Error 0.516004034
Observations 5

ANOVA
df SS MS F Significance F
Regression 1 74.40122 74.40122 279.4305 0.000466115
Residual 3 0.79878 0.26626
Total 4 75.2

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower
95.0% Upper 95.0%
Intercept -0.231707317 0.522259 -0.44366 0.687333 -1.893769644
1.430355 -1.89377 1.430355
A 1.506097561 0.090098 16.71618 0.000466 1.219364569 1.792831 1.219365
1.792831

TRS -

Can anybody explain why I get different values for regression analysis
using the data analysis under "tools" and when the add trendline is used.
...

Be sure to use an XY (Scatter) chart type.

(If you use a Line chart type, the trendline uses 1,2,3,... for the X
values.)

- Mike

www.mikemiddleton.com

Also be sure to use sufficient digits in your formula. Excel likes to provide one or
two significant figures when 10 or 12 would show any real difference in the results.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Michael R Middleton wrote:

TRS -


Can anybody explain why I get different values for regression analysis
using the data analysis under "tools" and when the add trendline is used.
...


Be sure to use an XY (Scatter) chart type.

(If you use a Line chart type, the trendline uses 1,2,3,... for the X
values.)

- Mike

www.mikemiddleton.com

The statistical tools in Excel are not reliable.
Use a statistical package such as S-Plus or do the
calculations by hand if they are relatively small.

Josie

-----Original Message-----
Can anybody explain why I get different values for
regression analysis using
the data analysis under "tools" and when the add
trendline is used. Note
the intercept is not set to zero.

A B
2 3
3 4
5 7
7 11
9 13



















SUMMARY OUTPUT

Regression Statistics
Multiple R 0.994674781
R Square 0.989377919
Adjusted R Square 0.985837225
Standard Error 0.516004034
Observations 5

ANOVA
df SS MS F Significance F
Regression 1 74.40122 74.40122 279.4305 0.000466115
Residual 3 0.79878 0.26626
Total 4 75.2

Coefficients Standard Error t Stat P-value Lower
95% Upper 95% Lower
95.0% Upper 95.0%
Intercept -0.231707317 0.522259 -0.44366 0.687333 -
1.893769644
1.430355 -1.89377 1.430355
A 1.506097561 0.090098 16.71618 0.000466
1.219364569 1.792831 1.219365
1.792831

When properly used (see posts by Mike Middleton and Jon Peltier) Excel's
polynomial trendline fit is more accurate than lm() in S-Plus (unless
you explicitly do a poly.transform of an orthogonal polynomial fit) and
most other dedicated statistics packages. Excel's statistical fitting
functions are mathematically exact, but implemented in a way that does
not minimize rounding errors with challenging data sets (the OP's data
set is not challenging). For univariate and bivariate statistics, there
are easy workarounds to handle even challenging data
http://groups.google.com/groups?selm...40mediaone.net
Alternately, Excel 2003 uses much improved algorithms.

Excel does not and probably never will have the statistical power and
flexibility of a dedicated statistics package, but that does not mean
that it is not appropriate for simple calculations such as the OP was
attempting.

Jerry

Josie wrote:

The statistical tools in Excel are not reliable.
Use a statistical package such as S-Plus or do the
calculations by hand if they are relatively small.

Josie


-----Original Message-----
Can anybody explain why I get different values for

regression analysis using

the data analysis under "tools" and when the add

trendline is used. Note

the intercept is not set to zero.

A B
2 3
3 4
5 7
7 11
9 13

....