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?