A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Linest - Why did they do that?



 
 
Thread Tools Display Modes
  #1  
Old March 10th 05, 07:01 PM
cseeton
external usenet poster
 
Posts: n/a
Default 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  
Old March 10th 05, 07:59 PM
Eric
external usenet poster
 
Posts: n/a
Default

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  
Old March 11th 05, 01:45 PM
Jerry W. Lewis
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 07:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.