Reversed Array
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
--
Biff
Microsoft Excel MVP
"Jerry W. Lewis" wrote in message
...
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
" wrote:
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"
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.
|