Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How do I reverse the linest coefficients?  Excel Worksheet Functions  
LINEST maximum number of predictor variables  Excel Worksheet Functions  
Excluding 0s and blanks from a LINEST function  Excel Worksheet Functions  
how can i fill a table with values from repeated regressions  Excel Worksheet Functions  
Trend Lines (or Linest?)  Excel Worksheet Functions 