weekly average for daily data

Topic

New Reply

I have daily data for which I would like to calculate a weekly average. A bigger hammer approach is to do a 7-day running average and delete 6 points, but there has to be a better way? I’d start over for each year, to get rid of the 0.25 day problem.

Thanks.

Reply | Quote

Replies

If we presume that a has dates, B has the data. You can add an intermediate column in C. In C1 enter
=Weeknum(A1)
Copy C1 down the column to calculate the week for each date
Then in D1 you can enter:
=AVERAGEIF(C:C,ROW(),B:B)

and copy this down the column. In row 1 will be the average for week1, in row2 the average for week2 etc

Steve
PS there is a 0.25 when working with days in a year. When working with weeks in a year, there are other issues since there are 14 different calendar archtypes, week1 may have 1 to 7 days. Week 53 may have 1 to 7 days. There is also a possibility a year may have a day in the 54th week…

Reply | Quote

Thanks!

My data has headers, so I changed it to:
=AVERAGEIF(C:C,ROW()-1,B:B)

And for my purposes, the first 52 weeks of the year will suffice

Reply | Quote

Be aware that weeknum starts on Sunday, so week 1 may not have a full 7 days….

Steve

Reply | Quote