mistake in formula

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?

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

"pm" <[email protected]> wrote in message news:[email protected]...
> 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?

Mike Middleton wrote:

> 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.

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

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

LINEST in Excel versions prior to 2003 used incorrect formulas for SSreg, F,
and RSQ when the intercept term is forced through zero

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

Jerry

"pm" wrote:

> 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?
>

pm -

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

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! <

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

"pm" <[email protected]> wrote in message news:[email protected]...
> Mike Middleton wrote:
>
>> 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.
>
> Yes, i know about multicolineariti, but as you see - it is ADL model..
>
> anyway, linest shouldn't give r.squered less than zero!