lookup last nth number of items in a list
 

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?

lookup last nth number of items in a list
 

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 InsertNameDefine

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:I NDEX(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.

--
Biff
Microsoft Excel MVP


"D. Stacy" .(remove_this_part). wrote in message
...
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?

lookup last nth number of items in a list
 

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:A10000)*(WEEKDAY(A1:A1000)=2)*(A1:A10 00TODAY()-(3*7)),C1:C1000))

OR

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


If this post helps click Yes
---------------
Jacob Skaria


"D. Stacy" wrote:

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?

lookup last nth number of items in a list
 

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

'for last 3 mondays
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(3*7)),C1:C1000))

'Replace n with the number or cell reference
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(n*7)),C1:C1000))


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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:A10000)*(WEEKDAY(A1:A1000)=2)*(A1:A10 00TODAY()-(3*7)),C1:C1000))

OR

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


If this post helps click Yes
---------------
Jacob Skaria


"D. Stacy" wrote:

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?

lookup last nth number of items in a list
 

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

"D. Stacy" .(remove_this_part). wrote in message
...
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?

lookup last nth number of items in a list
 

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.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Oops; it is average..Try the below array formula

'for last 3 mondays
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(3*7)),C1:C1000))

'Replace n with the number or cell reference
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(n*7)),C1:C1000))


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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:A10000)*(WEEKDAY(A1:A1000)=2)*(A1:A10 00TODAY()-(3*7)),C1:C1000))

OR

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


If this post helps click Yes
---------------
Jacob Skaria


"D. Stacy" wrote:

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?

lookup last nth number of items in a list
 

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


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Oops; it is average..Try the below array formula

'for last 3 mondays
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(3*7)),C1:C1000))

'Replace n with the number or cell reference
=AVERAGE(IF((A1:A10000)*(WEEKDAY(A1:A1000)=2)*(A1 :A1000TODAY()-(n*7)),C1:C1000))


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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:A10000)*(WEEKDAY(A1:A1000)=2)*(A1:A10 00TODAY()-(3*7)),C1:C1000))

OR

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


If this post helps click Yes
---------------
Jacob Skaria


"D. Stacy" wrote:

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?