LINEST parameter output order

For years I've been regressing vapor pressure data with a 4 parameter equation in Excel using the built in matrix functions. From this group, I learned that I could perform the same regression using the LINEST function, which would clean up my spreadsheet a little bit, and reduce the work of building the spreadsheet. And it works just fine, with one minor annoyance. Hopefully I can explain.

The specific form of the function is ln(y)=A+B/x+Cln(x)+Dx^6

I set up the matrix as:
f1(x1),f2(x1),f3(x1),f4(x1),g(y1)
f1(x2),f2(x2),f3(x2),f4(x2),g(y2)
and so on
where f1=1, f2=1/x, f3=ln(x), f4=x^6, g=ln(y)

When I would use the matrix functions (mmult, minverse), the parameters would be output in the same order as I had set up the matrix (A,B,C,D). When I use LINEST, the parameters come out backwards (D,C,B,A).

Of course, the workaround is to arrange the input matrix backwards. However, it often helps me keep things straight if the output is in the same order as the input.

Anyone else ran across (and maybe been annoyed) by this?

LOL! Yes, it can be annoying and at times it feels like the result of
LINEST is the 'wrong way around.' But, it's a small annoyance that I have
learnt to live with.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <MrShorty.24d22m_1141837800.6566@excelforum-nospam.com>,
MrShorty.24d22m_1141837800.6566@excelforum-nospam.com says...
>
> For years I've been regressing vapor pressure data with a 4 parameter
> equation in Excel using the built in matrix functions. From this
> group, I learned that I could perform the same regression using the
> LINEST function, which would clean up my spreadsheet a little bit, and
> reduce the work of building the spreadsheet. And it works just fine,
> with one minor annoyance. Hopefully I can explain.
>
> The specific form of the function is ln(y)=A+B/x+Cln(x)+Dx^6
>
> I set up the matrix as:
> f1(x1),f2(x1),f3(x1),f4(x1),g(y1)
> f1(x2),f2(x2),f3(x2),f4(x2),g(y2)
> and so on
> where f1=1, f2=1/x, f3=ln(x), f4=x^6, g=ln(y)
>
> When I would use the matrix functions (mmult, minverse), the parameters
> would be output in the same order as I had set up the matrix (A,B,C,D).
> When I use LINEST, the parameters come out backwards (D,C,B,A).
>
> Of course, the workaround is to arrange the input matrix backwards.
> However, it often helps me keep things straight if the output is in the
> same order as the input.
>
> Anyone else ran across (and maybe been annoyed) by this?
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=520182
>
>

=INDEX({4,3,2,1},LINEST(...))

Jerry

"MrShorty" wrote:

>
> For years I've been regressing vapor pressure data with a 4 parameter
> equation in Excel using the built in matrix functions. From this
> group, I learned that I could perform the same regression using the
> LINEST function, which would clean up my spreadsheet a little bit, and
> reduce the work of building the spreadsheet. And it works just fine,
> with one minor annoyance. Hopefully I can explain.
>
> The specific form of the function is ln(y)=A+B/x+Cln(x)+Dx^6
>
> I set up the matrix as:
> f1(x1),f2(x1),f3(x1),f4(x1),g(y1)
> f1(x2),f2(x2),f3(x2),f4(x2),g(y2)
> and so on
> where f1=1, f2=1/x, f3=ln(x), f4=x^6, g=ln(y)
>
> When I would use the matrix functions (mmult, minverse), the parameters
> would be output in the same order as I had set up the matrix (A,B,C,D).
> When I use LINEST, the parameters come out backwards (D,C,B,A).
>
> Of course, the workaround is to arrange the input matrix backwards.
> However, it often helps me keep things straight if the output is in the
> same order as the input.
>
> Anyone else ran across (and maybe been annoyed) by this?
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=520182
>
>

That's a good workaround, Jerry. I hadn't yet considered using the INDEX function to invert the order like that.

As with a lot of things, I guess it's just a matter of getting used to doing things a new way.

Glad it helped.

My best guess for the reason that MS presents things in the order it does,
is that if you are sequentially testing terms for model selection, then at
each stage you only need the 1st 2 rows of the 1st column (assuming that you
can indepenently arrive at the df).

Jerry

"MrShorty" wrote:

>
> That's a good workaround, Jerry. I hadn't yet considered using the
> INDEX function to invert the order like that.
>
> As with a lot of things, I guess it's just a matter of getting used to
> doing things a new way.
>
>
> --
> MrShorty
> ------------------------------------------------------------------------
> MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
> View this thread: http://www.excelforum.com/showthread...hreadid=520182