PC Review


Reply
Thread Tools Rate Thread

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
 
Reply With Quote
 
 
 
 
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

 
Reply With Quote
 
 
 
 
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
 
Reply With Quote
 
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
The multiple regression tool - linear or non-linear regression? Statistical interest Microsoft Excel Misc 2 7th Feb 2008 12:01 AM
Excel 2003 Regression using more than 1 independent variable =?Utf-8?B?QWxleA==?= Microsoft Excel Misc 2 1st Feb 2007 07:29 AM
select offset (variable ,1) to offset(variable ,variable) Buffyslay Microsoft Excel Programming 1 15th Nov 2006 12:45 PM
add a second independent variable, doing regression in excel? =?Utf-8?B?Y2hlZWtz?= Microsoft Excel Misc 1 3rd Apr 2006 12:03 AM
Regression - Standard Error X Variable 1 =?Utf-8?B?Q2xhdWRlIFM=?= Microsoft Excel Worksheet Functions 2 19th Nov 2005 11:08 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:23 PM.