Re: LINEST Issue - forced crossing zero R2 is wrong?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

To clarify, in Excel 2003 with intercept forced to zero

LINEST is correct

ATP regression tool is correct except for adjusted R2, which should be
1-(1-R2)*n/(n-p)
where p is the number of parameters in the model (1 for simple linear
regression with no intercept). ATP incorrectly uses 1-(1-R2)*(n-1)/(n-1-p)

The chart trendline R2 is wrong.

Jerry

"Mike Middleton" wrote:

Tom -

This problem persists in Excel 2003 and 2007. See Case 3 in the Microsoft
Knowledge Base article 214230 "Incorrect output is returned when you use the
Linear Regression (LINEST) function in Excel"

http://support.microsoft.com/kb/214230

- Mike

http://www.MikeMiddleton.com



<tommoulds@xxxxxxxxx> wrote in message
news:02de0b45-5396-41c6-bc04-36c9f2580ada@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi

I am having real trouble with some simple linear regressions in
Excel. I would be excellent if someone could help.

I have various sets of data (one independent variable) that I am
trying to calculate R-squared for, with the Y intercept forced to 0
and without. When i dont have the intercept forced through zero all
my results seem to be acceptable. When I force crossing zero all my R-
squared values are very near 1. I would expect them to all get
slightly worse (most of the data is positively linearly correlated.)

For example for one set of data I get 0.522 for my R-Squared from
Linest with const as true (not forcing zero.) When I change the const
to zero (force through zero) I get 0.984. This is clearly wrong. I
think it should be about 0.49.

Has anyone else seen this? I am aware that the regression stats on
graphs in excel are incorrect but I though LINEST was ok. I am using
Excel 2003.

Thanks

Tom



.

Relevant Pages

  • RE: Excel 2003 Linest Function Bug
    ... Several examples have been published in these newsgroups were LINEST in Excel ... All of the zero parameter estimate problems I have seen up until now dealt ... Is there any fix by Microsoft in the works or is there something I can do to ...
    (microsoft.public.excel.worksheet.functions)
  • Re: LINEST Issue - forced crossing zero R2 is wrong?
    ... Linear Regression (LINEST) function in Excel" ... When I force crossing zero all my R- ...
    (microsoft.public.excel)
  • Re: regression analysis
    ... in Excel 2004 there's a nasty formula bug. ... Or you could use the regression tools in FreeMat, Octave, etc. ... which includes a Regression tool (which can be used for simple and multiple ... you can always use Excel's built-in array-entered LINEST worksheet ...
    (microsoft.public.mac.office.excel)
  • Re: Excel 2008: Analysis Add-in?
    ... versions of Excel. ... LINEST can be used for multiple regression with up to sixteen explanatory ... LINEST does not depend on the availability of the Analysis ToolPak add-in. ...
    (microsoft.public.mac.office.excel)
  • RE: R2 for regression with 2 depending variable
    ... LINEST in old versions of Excel did indeed calculate R2 incorrectly when the ... "Gaspard Enaud" wrote: ... I want a regression with a forced zero intercept. ...
    (microsoft.public.excel.crashesgpfs)