If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 .... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
EXCEL 2002: How do I user/simulate a Timer event in Excel form | JohnF | Worksheet Functions | 6 | September 23rd, 2004 04:56 PM |
Save as Web Page in Excel 2002 | Bob | New Users | 1 | June 23rd, 2004 02:49 PM |
Excel 2000 Analysis Toolpack add-in | Kirk Rensmeyer | Worksheet Functions | 2 | December 18th, 2003 11:07 PM |
Excel 2002 (XP) startup load excel feature | FL | Setting up and Configuration | 0 | November 19th, 2003 04:13 PM |
Are cell formats the same between Excel 2000 and 2002? | Chucknnita | Worksheet Functions | 1 | October 17th, 2003 03:16 AM |