lookup last nth number of items in a list

  • Thread starter Thread starter D. Stacy
  • Start date Start date
D

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?
 
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.
 
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
 
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
 
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:D). 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:D)))*(C4:C27))/F4

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
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.
 
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
 
Back
Top