Summarize daily data into weekly average

Hey Everyone

First time poster after hours of searching, be gentle please!

I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).

I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this?

Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used? Many many thanks in advance,

AR

Your question is close to the issue that I had the other day, except I wanted a monthly average.
see thread: Click Me!

Based on what I learned from that thread I suggest the following array formula (Ctrl-Shift-Enter to get the curly brackets)
{=AVERAGE(IF(data!$A$2:$A$1000>=A21,IF(data!$A$2:$A$1000<A22,data!$B$2:$B$1000,FALSE),FALSE))}

where
data!$A$2:$A$1000 is the range of daily dates
data!$B$2:$B$1000 is the range of daily data
A21 is the start of the week being averaged
A22 is the start of the following week

see it in use in the attached.

Hey thank you!

That practically got the job done, although I had to tweak it a little:
Interestingly, While MONTH(x) seems to work within the IF function, WEEKNUM(x) doesn't. I had to create a new column with the weeknum value but that didn't incorporate the year. e.g. the first week of 2007 and 2008 both had weeknum value 1.

So I had to create a new function for each year, but since I'm only examining a couple of years it wasn't too much work. For instance, for 2007 I consider only the first 252 values,

{=AVERAGE(IF($D$2:$D$252=F2,$B$2:$B$252,FALSE))}
where
D is the week which each daily data falls under
F is each week of 2007 and
B is my daily data

And for 2008 I look at the next 252 values

{=AVERAGE(IF($D$253:$D$505=F54,$B$253:$B$505,FALSE))}

I've included my file for reference.

Before I mark this thread as solved, is there an easier way to incorporate year? It doesn't matter so much for me but it might help future users.

Thanks again!

AR

Wouldn't a Pivottable be more efficient? You can group by 7 days, months, years or quarters

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save