Building Non-Contiguous Arrays For Use With Linest
 

Can anyone think of a simple way to accomplish this:

Goal - use the Linest function to create a regression against a series
of dynamic ranges.

Example:

Column A Column B Column C
Row 1 DATE DOW VALUES

1/1/08 =weekday(A2) V1
. . .
. . .
. . .
12/31/08 . .


Using the Linest function, I'd like to create a regression that looks
at

n # of rows back (so flexible enough to include all data or from this
point in time to a limited number of days/weeks back)


Tuesdays Values = F ( Prior Monday, Prior Sunday, Prior Saturday,
Prior Friday, Prior Thur, Prior Wed, Prior Tues)

I understand the linest function and how to use Index to find all the
coefficients, rsquared, errors, etc. so no worries there.
But how can I create the ranges that pull the data that select only
data from a particular day of the week. Yes I know I could
take lots of real estate and repeat the values across multiple
columns. But that seems like such a waste since the values already
exist.

Any thoughts to this puzzle?

Building Non-Contiguous Arrays For Use With Linest
 

To return an array of values (Column C) where the corresponding weekday
(Column B) is Thursday (Weekday = 5)...

N(OFFSET(C2:C100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

To return an array of dates (Column A) where the corresponding weekday
(Column B) is Thursday (Weekday = 5)...

N(OFFSET(A2:A100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

Use these as your arguments for the LINEST function.

Hope this helps!

In article
,
Marston wrote:

Can anyone think of a simple way to accomplish this:

Goal - use the Linest function to create a regression against a series
of dynamic ranges.

Example:

Column A Column B Column C
Row 1 DATE DOW VALUES

1/1/08 =weekday(A2) V1
. . .
. . .
. . .
12/31/08 . .


Using the Linest function, I'd like to create a regression that looks
at

n # of rows back (so flexible enough to include all data or from this
point in time to a limited number of days/weeks back)


Tuesdays Values = F ( Prior Monday, Prior Sunday, Prior Saturday,
Prior Friday, Prior Thur, Prior Wed, Prior Tues)

I understand the linest function and how to use Index to find all the
coefficients, rsquared, errors, etc. so no worries there.
But how can I create the ranges that pull the data that select only
data from a particular day of the week. Yes I know I could
take lots of real estate and repeat the values across multiple
columns. But that seems like such a waste since the values already
exist.

Any thoughts to this puzzle?

Building Non-Contiguous Arrays For Use With Linest
 

On May 17, 1:34*pm, Domenic wrote:
To return an array of values (Column C) where the corresponding weekday
(Column B) is Thursday *(Weekday = 5)...

N(OFFSET(C2:C100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

To return an array of dates (Column A) where the corresponding weekday
(Column B) is Thursday (Weekday = 5)...

N(OFFSET(A2:A100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

Use these as your arguments for the LINEST function.

Hope this helps!

In article
,

*Marston wrote:
Can anyone think of a simple way to accomplish this:

Goal - use the Linest function to create a regression against a series
of dynamic ranges.

Example:

* * * * * * *Column A * * * Column B * * * * *Column *C
Row 1 * *DATE * * * * * *DOW * * * * * * * * *VALUES

* * * * * * *1/1/08 * * * * * * =weekday(A2) * * V1
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * *12/31/08 * * * * . * * * * * * * * * * * * .

Using the Linest function, I'd like to create a regression that looks
at

n # of rows back (so flexible enough to include all data or from this
point in time to a limited number of days/weeks back)
Thanks!!!!


Tuesdays Values = F ( Prior Monday, Prior Sunday, Prior Saturday,
Prior Friday, Prior Thur, Prior Wed, Prior Tues)

I understand the linest function and how to use Index to find all the
coefficients, rsquared, errors, etc. so no worries there.
But how can I create the ranges that pull the data that select only
data from a particular day of the week. Yes I know I could
take lots of real estate and repeat the values across multiple
columns. But that seems like such a waste since the values already
exist.

Any thoughts to this puzzle?

Building Non-Contiguous Arrays For Use With Linest
 

Something seems a bit off.
Shouldn't Small have an array and a k value as parameters? What's the
K value in this equation?


On May 17, 2:15*pm, Marston wrote:
On May 17, 1:34*pm, Domenic wrote:

To return an array of values (Column C) where the corresponding weekday
(Column B) is Thursday *(Weekday = 5)...

N(OFFSET(C2:C100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

To return an array of dates (Column A) where the corresponding weekday
(Column B) is Thursday (Weekday = 5)...

N(OFFSET(A2:A100,SMALL(IF(B2:B100=5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1))

Use these as your arguments for the LINEST function.

Hope this helps!

In article
,

*Marston wrote:
Can anyone think of a simple way to accomplish this:

Goal - use the Linest function to create a regression against a series
of dynamic ranges.

Example:

* * * * * * *Column A * * * Column B * * * * *Column *C
Row 1 * *DATE * * * * * *DOW * * * * * * * * *VALUES

* * * * * * *1/1/08 * * * * * * =weekday(A2) * * V1
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * * . * * * * * * * * * *. * * * * * * * * * * * * .
* * * * * * *12/31/08 * * * * . * * * * * * * * * * * * .

Using the Linest function, I'd like to create a regression that looks
at

n # of rows back (so flexible enough to include all data or from this
point in time to a limited number of days/weeks back)

Thanks!!!!





Tuesdays Values = F ( Prior Monday, Prior Sunday, Prior Saturday,
Prior Friday, Prior Thur, Prior Wed, Prior Tues)

I understand the linest function and how to use Index to find all the
coefficients, rsquared, errors, etc. so no worries there.
But how can I create the ranges that pull the data that select only
data from a particular day of the week. Yes I know I could
take lots of real estate and repeat the values across multiple
columns. But that seems like such a waste since the values already
exist.

Any thoughts to this puzzle?

Building Non-Contiguous Arrays For Use With Linest
 

In article
,
Marston wrote:

Something seems a bit off.
Shouldn't Small have an array and a k value as parameters? What's the
K value in this equation?

The array for the SMALL function is...

IF(B2:B100=5,ROW(B2:B100)-ROW(B2))

The K value for the SMALL function is actually an array of values...

ROW(INDIRECT("1:"&COUNTIF(B2:B100,5)))

As I mentioned in my previous post, the two formulas I offered each
return an array of values and are used as the arguments for the LINEST
function.

For example, select two cells in a horizontal range, let's say E2:F2,
enter something like the following formula...

=LINEST(N(OFFSET(C2:C100,SMALL(IF(B2:B100=5,ROW(B2 :B100)-
ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1)),N(OFFSET(A2:A100,SM ALL(IF(B2:B100=
5,ROW(B2:B100)-ROW(B2)),ROW(INDIRECT("1
:"&COUNTIF(B2:B100,5)))),0,1)))

....and then confirm with CONTROL+SHIFT+ENTER, not just ENTER. If done
correctly, Excel will automatically place curly brackets around the
formula.

Does this help?