# Finding Averages by Month from a multi year daily data range

1. ## 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. ## Re: Finding Averages by Month from a multi year daily data range

Perhaps a simple Pivot Table?

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

Unfortunately I have to do this without a pivot table.

4. ## 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.

5. ## 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.

6. ## 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. ## 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. ## Re: Finding Averages by Month from a multi year daily data range

You're welcome. Thanks for the feedback!

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

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