+ Reply to Thread
Results 1 to 5 of 5

Averaging Quarterly and Annual Data

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Averaging Quarterly and Annual Data

    Hi there,

    I have a table that shows percent changes for a series of dates that range from January 2000 - June 2013. I'd like to put together a table with quarterly and annual averages for this data. I have formulas to identify which quarter and year a date falls into, but I wanted to see if anyone can help with a formula that will pull out the data points for each quarter and produce the average instead of manually selecting all of the cells that fall into each quarter and averaging these.

    Attaching my spreadsheet, I have two tabs with similar charts in which I'd like to insert these annual and quarterly averages.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Averaging Quarterly and Annual Data

    i3 =AVERAGEIFS($C$3:$C$2306,$E$3:$E$2306,H3,$D$3:$D$2306,RIGHT(G3)+0)

    L3 =AVERAGEIF($H$3:$H$2306,K3,$C$3:$C$2306)
    Last edited by martindwilson; 07-10-2013 at 06:11 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Averaging Quarterly and Annual Data

    This works great!!!!

    Do you know how I would modify the formula to pull in annual averages?

  4. #4
    Registered User
    Join Date
    06-11-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Averaging Quarterly and Annual Data

    I must be blind! Missed that second formula. Thanks a ton!

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Averaging Quarterly and Annual Data

    second formula is wrong i referenced $H$3:$H$2306 not $E$3:$E$2306 oops
    it should be =AVERAGEIF($E$3:$E$2306,K3,$C$3:$C$2306)

+ 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