To get cells with the LINEST values in reverse order use:
=INDEX(LINEST($A$1:$A$7,$C$1:$E$7),4) for the intercept
=INDEX(LINEST($A$1:$A$7,$C$1:$E$7),3) for next coefficient
etc
Note: I experimented with a smaller data set but the method should be clear.
No need to use CTR+SHIFT+ENTER to commit, just ENTER will do
Now use SUMPRODUCT with theses cells and the range with the variables.
I do not see much merit is forcing this into one formula without the use of
a 'helper' range of cells with the INDEX formulas
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
...
Hi - I'm trying to use LINEST to create an automated, rolling multiple
regression.
In otherwords, rather than use the Analysis Toolpak over and over
again to run a regression with one more incremental set of data, I'd
like to accomplish the same thing using LINEST. Then using the output
of this, use the coefficients to created trended data.

Unfortunately, there is one oddity with LINEST. It returns the values
in reverse order.
Let's say you had a regression of data in 4 columns, A through D.
If you used the Analysis tookpak, it would give the coefficients in
the following order:

Intercept, Coefficient Column A, Coefficient Column B, etc.

But when you use LINEST it gives you the list in the exact opposite
order. The difficulty here is that
to create a trend forecast using those coefficients, you either need
to reverse the order of the coefficents or your raw data. I've seen
several suggestions on how to reverse an array across multiple cells -
but that won't work for what I need to accomplish.

Eventually, what I'd like to be able to do is something like
SUMPRODUCT(LINEST($A$1:$A$100,$c$1:$H$100),$B101:$ H101)

In column B, I'd store the value "1" so the intercept is always
multiplied by 1.

Like I said however, LINEST reverses everything, so what I really need
is:

SUMPRODUCT( reverse of (LINEST(......)),$B101:$H101)

Any thoughts?


Thanks in advance.

Marston Gould