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?

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. David Heiser

    New LINEST For Excel 2003

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

    Array Function LINEST

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

    LINEST with Autofilter

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

    LINEST

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

    Linest

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

Share This Page