LINEST parameter output order

[MrShorty]

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?

 

[Tushar Mehta]

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

 

[Guest]

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

Jerry

 

[MrShorty]

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.

 

[Guest]

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