If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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). Thanks in advance cseeton 
Ads 
#2




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). > > Thanks in advance > cseeton > 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How do I reverse the linest coefficients?  cseeton  Excel Worksheet Functions  1  March 11th 05 01:09 PM 
LINEST maximum number of predictor variables  Peter N  Excel Worksheet Functions  7  February 17th 05 10:14 AM 
Excluding 0s and blanks from a LINEST function  Disco  Excel Worksheet Functions  4  February 4th 05 04:54 AM 
how can i fill a table with values from repeated regressions  buragotch  Excel Worksheet Functions  4  January 1st 05 01:25 PM 
Trend Lines (or Linest?)  Martinaire  Excel Worksheet Functions  2  November 26th 04 06:08 PM 