R2 for regression with 2 depending variable

[Gaspard Enaud]

Hi,

I'm using the linest function to calculate a regression between one and two
other variables.

I want a regression with a forced zero intercept.

It appears that the R2 is not correct.

Is there a way to obtain a correct R2 ?

Thanks a lot in any case,

Gaspard Enaud

 

[Shane Devenshire]

Hi,

I plotted some data and used LINEST forcing the intercept throught 0 and
compared the Rsq results from LINEST with the plot, they are not the same. I
suspect this is a known bug, but I will post it to Microsoft, none the less.

 

[Jerry W. Lewis]

You have omitted a key piece of information, namely the version of Excel that
you are using.

LINEST in old versions of Excel did indeed calculate R2 incorrectly when the
intercept was forced through zero, but that was fixed by 2003, and AFAIK MS
did not re-break it in 2007.

If you are using an older version of Excel, then the correct calculation is
=MMULT(TRANSPOSE(TREND(ydata,xdata,,0)),TREND(ydata,xdata,,0))/SUMSQ(ydata)

If you are using a newer version of Excel, then likely your expectation is
what is in error. Two examples with certified correct results are
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/lls/data/LINKS/DATA/NoInt2.dat

Jerry

 

[Jerry W. Lewis]

The R2 calculation with zero intercept can be further simplified to
=SUMSQ(TREND(ydata,xdata,,0))/SUMSQ(ydata)

Jerry


:

....

If you are using an older version of Excel, then the correct calculation is
=MMULT(TRANSPOSE(TREND(ydata,xdata,,0)),TREND(ydata,xdata,,0))/SUMSQ(ydata)

....