[[email protected]]

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
--
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

[[email protected]]

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

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

[[email protected]]

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.

[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

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

[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

--
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.

[[email protected]]

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

[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

" wrote:

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

[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

"Jerry W. Lewis" wrote:

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

[Travis]

I don't know how it works, or if it will work for your needs, but the following equation gives the coeficients in the reverse order:
=TRANSPOSE(MMULT(MINVERSE(MMULT(TRANSPOSE(xCol^{0, 1,2,3,4,5,6}),xCol^{0,1,2,3,4,5,6})), MMULT(TRANSPOSE(xCol^{0,1,2,3,4,5,6}), yCol)))

I found this at:
http://www.excelbanter.com/showthrea...2f4c& t=29786




On Saturday, December 22, 2007 8:43 AM Bernard Liengme wrote:

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
...


On Saturday, December 22, 2007 9:36 AM post_a_repl wrote:

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


On Saturday, December 22, 2007 4:15 PM post_a_repl wrote:

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:


On Saturday, December 22, 2007 5:22 PM T. Valko wrote:

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


=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
...


On Sunday, December 23, 2007 12:51 AM marston_goul wrote:

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


On Sunday, December 23, 2007 12:51 AM marston_goul wrote:

This would be a bit impractical as I am 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.


On Sunday, December 23, 2007 12:52 AM marston_goul 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 =3Dcount() around the entire thing and it returns a "1"




On Dec 22, 6:36=A0am, Jerry W. Lewis wrote:
n


On Sunday, December 23, 2007 7:53 AM post_a_repl wrote:

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


On Sunday, December 23, 2007 7:59 AM post_a_repl wrote:

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

"Jerry W. Lewis" wrote:


On Monday, December 24, 2007 8:48 PM marston_goul wrote:

On Dec 22, 2:22=A0pm, "T. Valko" wrote:

...
t
eet
n
ng
n

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


I have:
=3DINDEX(LINEST(data!$N$368:$N732,data!$A$368:$L73 2),12)
=3DINDEX(LINEST(data!$N$368:$N732,data!$A$368:$L73 2),11)
=2E
=2E
=2E
=3DINDEX(LINEST(data!$N$368:$N732,data!$A$368:$L73 2),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 =3D coefficients (from above) x (a368 through L368) + intercept
N369 =3D coefficients (from above) x (a369 through L369) + intercept
=2E
=2E
=2E
N732 =3D coefficient (from above) x (a732 through L732) + intercept