On Dec 22, 2:22*pm, "T. Valko" wrote:
Instead of reversing the output of LINEST (which I can't figure out how to
do),reversethe 2ndarray:

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} = horizontalarray

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

--
Biff
MicrosoftExcelMVP

"Jerry W. Lewis" wrote in ...

Then I guess you can't avoid using intermediate cells. *A few worksheet
functions seem to be implemented inconsistently with respect toarray
formulas, and INDEX appears to be one of them. *If you select 4 worksheet
cells andarrayenter
* =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 anarrayformula like
* =COUNT(INDEX(LINEST($A$1:$A$7,$C$1:$E$7),{4,3,2,1} ))
Excelonly sees a single value from it. *What the developers were thinking
here eludes me.

Jerry

" wrote:

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

On Dec 22, 6:36 am, Jerry W. Lewis wrote:
In which case, you simply add anotherarraycomponent to Bernard's
suggestion
* =INDEX(LINEST($A$1:$A$7,$C$1:$E$7),{4,3,2,1})
gives thearrayof estimates in the order that you desire.

Jerry

" wrote:
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.

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