lookup last nth number of items in a list

[D. Stacy]

I have list of date series data that updates every day (thus growing data
array); What I want is a formula that will me to constantly evualate the n
number of records. For example compute the average of the last 26 Monday's
from a data listing; the data is general date ordered but that is not a
given.

Date DOW Value
1/2/09 2 37
......


Any ideas?

 

[T. Valko]

This is very complicated.

Since you seem to be adding data we'll need to use dynamic ranges.

Dates = A1:An
Values to average = B1:Bn

Create these named formulas...(use the appropriate sheet name)

Goto Insert>Name>Define

Name: Dates
Refers to:

=Sheet2!$A$1:INDEX(Sheet2!$A:$A,COUNT(Sheet2!$B:$B))

Name: Nums
Refers to:

=Sheet2!$B$1:INDEX(Sheet2!$B:$B,COUNT(Sheet2!$B:$B))

Name: LastDate
Refers to:

=INDEX(Sheet2!$A:$A,COUNT(Sheet2!$B:$B))

Name: LastNum
Refers to:

=INDEX(Sheet2!$B:$B,COUNT(Sheet2!$B:$B))

D1 = number of instances you want to average

DOW = a number from 1 to 7 for the day of the week to average. 1 = Sunday
through 7 = Saturday

Note: there is no error checking in this formula. If there aren't n
instances to average you'll get an error.

Array entered** :

=AVERAGE(IF(WEEKDAY(LastDate:INDEX(Dates,LARGE(IF(WEEKDAY(Dates)=DOW,ROW(Dates)),D1)))=DOW,LastNum:INDEX(Nums,LARGE(IF(WEEKDAY(Dates)=DOW,ROW(Dates)),D1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Jacob Skaria]

Since your data gets updates every day you can try the below ** array **
formula. An array formula is same as normal formulas except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

'For the last 3 mondays
=SUM(IF((A1:A1000>0)*(WEEKDAY(A1:A1000)=2)*(A1:A1000>TODAY()-(3*7)),C1:C1000))

OR

'replace n with a cell reference to denote the number of mondays
=SUM(IF((A1:A1000>0)*(WEEKDAY(A1:A1000)=2)*(A1:A1000>(TODAY)-(n*7)),C1:C1000))


If this post helps click Yes

 

[Jacob Skaria]

Oops; it is average..Try the below array formula

'for last 3 monday
=AVERAGE(IF((A1:A1000>0)*(WEEKDAY(A1:A1000)=2)*(A1:A1000>TODAY()-(3*7)),C1:C1000))

'Replace n with the number or cell referenc
=AVERAGE(IF((A1:A1000>0)*(WEEKDAY(A1:A1000)=2)*(A1:A1000>TODAY()-(n*7)),C1:C1000))


If this post helps click Yes

 

[Ashish Mathur]

Hi,

You may try this array formula (Ctrl+Shift+Enter). G4 contains the day of
the week I.e. Monday, Tuesday, Wednesday etc. This formula will sum the
last 4 Mondays, Tuesdays etc. (as specified in cell G4). If you want to sum
the last 5, then change the D to E in column(A). F4 holds 4 I.e. last 4
Monday, Tuesday, Wednesday etc

=SUMPRODUCT((ROW(B4:B27)=LARGE((TEXT(B4:B27,"dddd")=G4)*ROW(B4:B27),COLUMN(A)))*(C4:C27))/F4

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

I do my best thinking while I'm sleeping. And, as is often the case, I
thought of a better solution while I was sleeping that uses the same logic
as you have used.

A - Find the last weekday date
B - Calculate the date backwards for n weeks
C - Get the average for the weekday from A to B

Nice and simple!

I would still use dynamic ranges and use cells to get the date range:

For the last weekday date...

F2:

=LOOKUP(2,1/(WEEKDAY(Dates)=2),Dates)

For the date n weeks backwards...

E2:

=F2-((n-1)*7)

Where n = the number of instances to average

Then, array entered** for the average:

=AVERAGE(IF((WEEKDAY(Dates)=2)*(Dates>=E2)*(Dates<=F2),Nums))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Jacob Skaria]

That happens with me too Biff..The first solution may not be always the best
one..and when I re-look at it (more often as you told while sleeping); will
get a better one....

If this post helps click Yes