+ Reply to Thread
Results 1 to 8 of 8

Finding Averages by Month from a multi year daily data range

  1. #1
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Finding Averages by Month from a multi year daily data range

    Hi,

    What I'm trying to do is find averages by month based off of a multi year daily data range.

    Let's say I have a larger daily time series in Column A, data to Average in Column B, & and Just months and Years in Column C. Like this:

    (A)Daily Dates for a few years (B) Matching data per each date (C) Jan '15, Feb '15, Mar '15...

    I've been trying to get Array's to work for this but the only one I could get to work was not returning the correct average. I've not had success with SUMPRODUCT. I've been beating myself up on this all day yesterday and I'm stuck. Any help is greatly appreciated.

    Thanks

  2. #2
    Forum Moderator Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    9,721

    Re: Finding Averages by Month from a multi year daily data range

    Perhaps a simple Pivot Table?
    You really don't have to give me rep points for moderating. It's part of the job. Keep rep points for those who find solutions

  3. #3
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: Finding Averages by Month from a multi year daily data range

    Unfortunately I have to do this without a pivot table.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Finding Averages by Month from a multi year daily data range

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.

    A file of that size in *.xlsx format will be about 10kb. We don't want to wade through 100's or 1000's of rows and dozens of columns of irrelevant data to find a solution.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: Finding Averages by Month from a multi year daily data range

    Hi,

    I've attached a small sample. The desired results are highlighted in green.
    Attached Files Attached Files

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Finding Averages by Month from a multi year daily data range

    If you have data for multiple years then you'll have to include that in the criteria.

    Data Range
    A
    B
    C
    D
    E
    1
    Date
    Variance
    ------
    Month
    Average
    2
    7/23/2015
    0.79
    Jul-15
    -0.09
    3
    7/24/2015
    -2.37
    Aug-15
    0.09
    4
    7/27/2015
    1.11
    Sep-15
    0.11
    5
    7/28/2015
    0.13
    6
    8/3/2015
    1.05
    7
    8/4/2015
    0.53
    8
    8/5/2015
    -2.37
    9
    8/26/2015
    0.16
    10
    8/27/2015
    3.40
    11
    8/28/2015
    -1.18
    12
    8/31/2015
    -0.98
    13
    9/1/2015
    1.68
    14
    9/2/2015
    -0.65
    15
    9/17/2015
    -1.96
    16
    9/18/2015
    0.52
    17
    9/21/2015
    0.52
    18
    9/22/2015
    0.52


    D2:D4 are the 1st of the month dates 7/1/2015, 8/1/2015 and 9/1/2015 formatted to display as mmm-yy.

    Then, this formula entered in E2 and copied down:

    =ROUND(AVERAGEIFS(B:B,A:A,">="&D2,A:A,"<="&EOMONTH(D2,0)),2)

  7. #7
    Forum Contributor
    Join Date
    10-16-2012
    Location
    Brooklyn
    MS-Off Ver
    Excel 2010 PC
    Posts
    137

    Re: Finding Averages by Month from a multi year daily data range

    Thanks! That EOMONTH was something I'd never heard of. Good to know. Works great.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Finding Averages by Month from a multi year daily data range

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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