Remember Me?

#1
March 10th 05, 06:01 PM
 cseeton Posts: n/a
Linest - Why did they do that?

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

cseeton

#2
March 10th 05, 06:59 PM
 Eric Posts: n/a

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

cseeton

#3
March 11th 05, 12:45 PM
 Jerry W. Lewis Posts: n/a

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

cseeton

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post cseeton Excel Worksheet Functions 1 March 11th 05 01:09 PM Peter N Excel Worksheet Functions 7 February 17th 05 10:14 AM Disco Excel Worksheet Functions 4 February 4th 05 04:54 AM buragotch Excel Worksheet Functions 4 January 1st 05 01:25 PM Martinaire Excel Worksheet Functions 2 November 26th 04 06:08 PM

All times are GMT +1. The time now is 06:22 AM.