# R2 for regression with 2 depending variable

Gaspard Enaud
Guest
Posts: n/a

 29th Jun 2009

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
Guest
Posts: n/a

 29th Jun 2009

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Gaspard Enaud" wrote:

> 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

Jerry W. Lewis
Guest
Posts: n/a

 29th Jun 2009
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/...ATA/NoInt1.dat
http://www.itl.nist.gov/div898/strd/...ATA/NoInt2.dat

Jerry

"Gaspard Enaud" wrote:

> 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

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

Jerry W. Lewis
Guest
Posts: n/a

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

Jerry

"Jerry W. Lewis" wrote:

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

....

-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Statistical interest Microsoft Excel Misc 2 7th Feb 2008 12:01 AM =?Utf-8?B?QWxleA==?= Microsoft Excel Misc 2 1st Feb 2007 07:29 AM Buffyslay Microsoft Excel Programming 1 15th Nov 2006 12:45 PM =?Utf-8?B?Y2hlZWtz?= Microsoft Excel Misc 1 3rd Apr 2006 12:03 AM =?Utf-8?B?Q2xhdWRlIFM=?= Microsoft Excel Worksheet Functions 2 19th Nov 2005 11:08 AM

Features