Building Non-Contiguous Arrays For Use With Linest

[Marston]

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?

 

[Domenic]

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!

 

[Marston]

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!






Thanks!!!!

 

[Marston]

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?

 

[Domenic]

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,SMALL(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?