+ Reply to Thread
Results 1 to 3 of 3

Calculate monthly, quarterly, and yearly averages from daily data

  1. #1
    Registered User
    Join Date
    05-28-2010
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    15

    Calculate monthly, quarterly, and yearly averages from daily data

    Dear Excel Geniuses,

    I have a column of daily data over many years (ignoring weekends and holidays) and would like to average them in the following monthly format:

    Jan 2010, 123
    Feb 2010, 456
    ...
    Dec 2010, 789
    Jan 2011, 1234
    Feb 2011, 5678
    ...
    etc.

    Then I would like to compile them in the following quarterly and yearly formats:

    Qtr 1, 2010, abc
    Qtr 2, 2010, xyz
    ...
    Qtr 1, 2011, def
    Qtr 2, 2011, ghi

    and
    2010, 123
    2011, 456
    ...

    Thanks for any help. I want a time series of the averaged data, but am having trouble getting a formula to work.

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Calculate monthly, quarterly, and yearly averages from daily data

    A Pivot table will be your best bet. These links may be helpful:
    http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/
    http://blogs.law.emory.edu/benchapma...ers-and-years/
    Please click the * icon below if I have helped.

  3. #3
    Registered User
    Join Date
    05-28-2010
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Calculate monthly, quarterly, and yearly averages from daily data

    Thanks much! I was not aware of the "grouping" feature in pivot tables. I was only able to total, average, etc. the data by month, quarter, etc. but this will give me the time series I want.

    Thanks again!

+ 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