Reversed Array

[marston_gould]

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

 

[Bernard Liengme]

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

 

[marston_gould]

This would be a bit impractical as I'm dealing with a 12+ variable
multiple regression.
I'd need to find someway to not have to store each of the regression
coefficients in unique cells.

 

[Jerry W. Lewis]

In which case, you simply add another array component to Bernard's suggestion
=INDEX(LINEST($A$1:$A$7,$C$1:$E$7),{4,3,2,1})
gives the array of estimates in the order that you desire.

Jerry

 

[marston_gould]

This looks good - except it doesn't seem to be passing all the
arguments. I tried it both as and as not an array (ctrl sft enter). I
also through a =count() around the entire thing and it returns a "1"

 

[Jerry W. Lewis]

Then I guess you can't avoid using intermediate cells. A few worksheet
functions seem to be implemented inconsistently with respect to array
formulas, and INDEX appears to be one of them. If you select 4 worksheet
cells and array enter
=INDEX(LINEST($A$1:$A$7,$C$1:$E$7),{4,3,2,1})
then you will get the four coefficients; but embedded in another function
within an array formula like
=COUNT(INDEX(LINEST($A$1:$A$7,$C$1:$E$7),{4,3,2,1}))
Excel only sees a single value from it. What the developers were thinking
here eludes me.

Jerry

 

[T. Valko]

Instead of reversing the output of LINEST (which I can't figure out how to
do), reverse the 2nd array:

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)

=SUMPRODUCT(LINEST(.....),N(OFFSET(B101,,{6,5,4,3,2,1,0})))

Note:

{6,5,4,3,2,1,0} = horizontal array

{6;5;4;3;2;1;0} = vertical array

 

[marston_gould]

Instead of reversing the output of LINEST (which I can't figure out how to
do),reversethe 2ndarray:


=SUMPRODUCT(LINEST(.....),N(OFFSET(B101,,{6,5,4,3,2,1,0})))

Note:

{6,5,4,3,2,1,0} = horizontalarray

{6;5;4;3;2;1;0} = verticalarray

Good tip - except that I'm trying to do both at the same time:


I have:
=INDEX(LINEST(data!$N$368:$N732,data!$A$368:$L732),12)
=INDEX(LINEST(data!$N$368:$N732,data!$A$368:$L732),11)
.
.
.
=INDEX(LINEST(data!$N$368:$N732,data!$A$368:$L732),1)

This is a multivariable regression so n368 through n732 represent the
Y values; A368-L732 hold the X values.

Linest would be trying to find best fit for

N368 = coefficients (from above) x (a368 through L368) + intercept
N369 = coefficients (from above) x (a369 through L369) + intercept
.
.
.
N732 = coefficient (from above) x (a732 through L732) + intercept

 

[Jerry W. Lewis]

What is wrong with
=TREND($A$1:$A$100,$c$1:$H$100,$C101:$H101)
(which also sidesteps the redundant 1's in column B)?

Jerry

 

[Jerry W. Lewis]

This was couched in terms of your OP, since in subsequent posts you were less
than specific on exactly what X values you were predicting on. Sorry if that
was confusing.

Jerry