Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    weekly average for daily data

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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...
    Last edited by sdckapr; 2013-11-14 at 17:16.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Be aware that weeknum starts on Sunday, so week 1 may not have a full 7 days....

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •