mistake in formula

P

pm

hello,

i have such a data:

Y X1 X2 X3
0.77% -5.23% 13.40% 20.12%
3.55% 0.77% 6.57% 13.40%
1.47% 3.55% 0.74% 6.57%
1.81% 1.47% -2.15% 0.74%
1.07% 1.81% -2.30% -2.15%
5.47% 1.07% 0.06% -2.30%
-2.23% 5.47% 2.85% 0.06%
-1.77% -2.23% 0.31% 2.85%
0.27% -1.77% 2.85% 0.31%

i use formula =LINEST(range1;range2;0;1)

and i get R.squared value -9.61%, what is obviously mistake

r.sq is always > 0 and < 1..

i use office xp

and as i'm reading now (http://support.microsoft.com/kb/828533/)

there are some differences between results in excel 2002 and excel 2003

so, can it cause this problem?
 
M

Mike Middleton

pm -

Excel 2003's LINEST shows R Squared of approximately 0.132.

But why are you doing this? Have you looked at the data? Plots of Y vs X1, Y
vs X2, and Y vs X3 show no linear relationship. There is multicollinearity,
i.e., the correlation between X2 and X3 is approximately 0.89. The results
of multiple linear regression with no intercept show for each coefficient
the standard error of the coefficient is at least twice as large as the
coefficient itself, indicating no significant statistical linear
relationship.

- Mike
www.mikemiddleton.com
 
P

pm

Mike said:
Excel 2003's LINEST shows R Squared of approximately 0.132.

But why are you doing this? Have you looked at the data? Plots of Y vs X1, Y
vs X2, and Y vs X3 show no linear relationship. There is multicollinearity,
i.e., the correlation between X2 and X3 is approximately 0.89. The results
of multiple linear regression with no intercept show for each coefficient
the standard error of the coefficient is at least twice as large as the
coefficient itself, indicating no significant statistical linear
relationship.
Click to expand...

Yes, i know about multicolineariti, but as you see - it is ADL model..

anyway, linest shouldn't give r.squered less than zero!
 
M

Mike Middleton

pm -
Yes, i know about multicolineariti, but as you see - it is ADL model. <
Click to expand...

Please enlighten me. Does an ADL model have some special exemption from the
usual common sense guidelines of (a) avoiding multicollinearity and (b)
obtaining estimated coefficients significantly different from zero?
anyway, linest shouldn't give r.squered less than zero! <
Click to expand...

Of course, not. You said you were reading

http://support.microsoft.com/default.aspx/kb/828533/

So you have seen the following explanation:

"The intercept argument should be set to FALSE only if you want to force the
regression line to go through the origin. For Excel 2002 and earlier,
setting this argument to FALSE always returns results that are not correct,
at least in the detailed statistics that are available from LINEST. This
article discusses this issue and provides a workaround. This problem has
been corrected in Excel 2003."

- Mike
www.mikemiddleton.com
 
Top