Linest - Why did they do that?

Discussion in 'Microsoft Excel Misc' started by Guest, Mar 10, 2005.

  1. Guest

    Guest Guest

    I am having difficulty with the order of the coefficients outputed by linest
    when doing a multivariable regression. My problem is that the order is
    reversed from the input x variables. While for displaying the coefficients I
    do not have a problem, the problem is that my sheet is setup so that the
    column order is x1,x2,x3,x4... and I would like to use
    mmult(inputs,transpose(output coefficient)) or Sum(inputs*ouput
    coefficients) ) to fill in dataspace in other operations.

    Any suggestions on a way reverse the order so that I can use it with matrix
    operations without creating new columns in reverse order (involves reverse
    thinking on my part).

    Thanks in advance
    cseeton
     
    Guest, Mar 10, 2005
    #1
    1. Advertisements

  2. Guest

    Eric Guest

    I don't know that my solution could even be called good, but I used a
    modified identiy matrix
    0 0 0 0 1
    0 0 0 1 0
    0 0 1 0 0
    0 1 0 0 0
    1 0 0 0 0
    to flip a column of data to that it would work with the index() function. I
    simply used mmult to multiply the column with the matrix and I had the data
    in the form I needed it. It will also work if the data is in a row.
    Unfortunately, this was the only work around I could find in Excel.

    "cseeton" <> wrote in message
    news:...
    > I am having difficulty with the order of the coefficients outputed by

    linest
    > when doing a multivariable regression. My problem is that the order is
    > reversed from the input x variables. While for displaying the

    coefficients I
    > do not have a problem, the problem is that my sheet is setup so that the
    > column order is x1,x2,x3,x4... and I would like to use
    > mmult(inputs,transpose(output coefficient)) or Sum(inputs*ouput
    > coefficients) ) to fill in dataspace in other operations.
    >
    > Any suggestions on a way reverse the order so that I can use it with

    matrix
    > operations without creating new columns in reverse order (involves reverse
    > thinking on my part).
    >
    > Thanks in advance
    > cseeton
    >
     
    Eric, Mar 10, 2005
    #2
    1. Advertisements

  3. You could use the INDEX() function to reverse the coefficients, as in
    =INDEX(LINEST(B1:B6,A1:A6),1,{2,1})

    My guess as to why LINEST works this way, is that if you are doing model
    selection (such as testing whether a quadratic term is a significant
    improvement over a linear fit), you can do the test without displaying
    all the columns of LINEST output. Yes, that's pretty weak, and I would
    have preseverd the order of input columns, but I didn't write it ...

    Jerry

    cseeton wrote:

    > I am having difficulty with the order of the coefficients outputed by linest
    > when doing a multivariable regression. My problem is that the order is
    > reversed from the input x variables. While for displaying the coefficients I
    > do not have a problem, the problem is that my sheet is setup so that the
    > column order is x1,x2,x3,x4... and I would like to use
    > mmult(inputs,transpose(output coefficient)) or Sum(inputs*ouput
    > coefficients) ) to fill in dataspace in other operations.
    >
    > Any suggestions on a way reverse the order so that I can use it with matrix
    > operations without creating new columns in reverse order (involves reverse
    > thinking on my part).
    >
    > Thanks in advance
    > cseeton
     
    Jerry W. Lewis, Mar 11, 2005
    #3
    1. Advertisements

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. David Heiser

    New LINEST For Excel 2003

    David Heiser, Nov 11, 2003, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    367
    Dana DeLouis
    Nov 13, 2003
  2. Luis Verme

    Array Function LINEST

    Luis Verme, Nov 30, 2003, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    342
    Jerry W. Lewis
    Dec 1, 2003
  3. paddy

    LINEST with Autofilter

    paddy, Feb 18, 2004, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    316
    paddy
    Feb 18, 2004
  4. Guest

    LINEST

    Guest, Sep 25, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    250
    Tushar Mehta
    Sep 26, 2004
  5. mnarasim

    Linest

    mnarasim, Sep 26, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    268
    Guest
    Sep 26, 2004
  6. mnarasim

    Linest

    mnarasim, Sep 26, 2004, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    516
    Jerry W. Lewis
    Sep 27, 2004
  7. lashio

    LinEst with operator

    lashio, Jun 27, 2005, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    184
    lashio
    Jun 27, 2005
  8. NlCO

    LINEST, LOGEST, GROWTH or TREND??

    NlCO, Aug 22, 2005, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    341
    Jerry W. Lewis
    Aug 23, 2005
Loading...