+ Reply to Thread
Results 1 to 6 of 6

Changing average

  1. #1
    Registered User
    Join Date
    11-12-2010
    Location
    Rome; Italy
    MS-Off Ver
    Excel 2003
    Posts
    14

    Changing average

    Hi,
    I have a quite stupid problm but I don't know how to solve it quickly.
    I have to calculate the weekly average for all the weeks of an year ( 52-53 weeks 9 and I have the data in days all in a column ( A)... how should I write a quick formula to copy in all the cell , insteda that doing =average(a1:a7), average(a8;a14) and so on? Thank you very much
    Attached Files Attached Files

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Changing average

    One way with Pivot table:
    Example.xls

  3. #3
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: Changing average

    One downside with a pivot table is that you have to manually refresh it, although that's not necessarily a problem.

    If you want formulae that automatically update, you could put down a list of your weeks and use an AVERAGEIF formula, copied down. You might find it easiest to write the conditions if you put in a helper column to calculate the week number from the date, perhaps using the WEEKNUM function.

  4. #4
    Registered User
    Join Date
    11-12-2010
    Location
    Rome; Italy
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Changing average

    Quote Originally Posted by zbor View Post
    One way with Pivot table:
    Attachment 89821
    I Think this could be a solution. Just one question: how di you put the data in weeks in the pivot table ( 1.1.2010-7.1.2010, and so on)?
    Thank you to both

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Changing average

    right click on your data and GROUP

    You can group by days, months, years or more of them

    (for example only months will take average for all Aprils from 2000 to 2010, and months+years will take average of April in 2000 separate than 2001, 2002 etc)...

    Just play with it slightly and you'll figure it out

  6. #6
    Registered User
    Join Date
    11-12-2010
    Location
    Rome; Italy
    MS-Off Ver
    Excel 2003
    Posts
    14

    Thumbs up Re: Changing average

    Quote Originally Posted by zbor View Post
    right click on your data and GROUP

    You can group by days, months, years or more of them

    (for example only months will take average for all Aprils from 2000 to 2010, and months+years will take average of April in 2000 separate than 2001, 2002 etc)...

    Just play with it slightly and you'll figure it out
    Amazing!!!! Thank you so much!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1