R2 for regression with 2 depending variable

Discussion in 'Microsoft Excel Crashes' started by Gaspard Enaud, Jun 29, 2009.

  1. 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
     
    Gaspard Enaud, Jun 29, 2009
    #1
    1. Advertisements

  2. 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
     
    Shane Devenshire, Jun 29, 2009
    #2
    1. Advertisements

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

    "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, Jun 29, 2009
    #3
  4. 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
     
    Jerry W. Lewis, Jun 30, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Adrianjay

    Regression

    Adrianjay, Nov 1, 2003, in forum: Microsoft Excel Crashes
    Replies:
    1
    Views:
    216
    Guest
    Nov 7, 2003
  2. rick

    Regression function in MS excel

    rick, Jan 27, 2004, in forum: Microsoft Excel Crashes
    Replies:
    2
    Views:
    352
  3. findljl

    Fatal errors in MS excel 2000 when running regression

    findljl, Apr 13, 2005, in forum: Microsoft Excel Crashes
    Replies:
    1
    Views:
    297
    vandenberg p
    Apr 14, 2005
  4. Guest

    Regression Analysis Error message

    Guest, Jun 1, 2005, in forum: Microsoft Excel Crashes
    Replies:
    3
    Views:
    2,401
    Guest
    Aug 4, 2005
  5. Guest

    Multiple Regression Output

    Guest, Jul 26, 2005, in forum: Microsoft Excel Crashes
    Replies:
    1
    Views:
    247
    Guest
    Aug 19, 2005
Loading...

Share This Page