Averageifs - Converting Weekly data to monthly average

[jasman922]

I am new to the Averageifs function, but it seems perfectly suited to what I am trying to do. I have weekly Data and want to convert to monthly average without having to manually adjust the range for each month. A piece of the sheet is below:

Weekly Date Values Monthly Date Average for the month

01/07/1967 208,000 Jan-67 #DIV/0!
01/14/1967 207,000 Feb-67 #DIV/0!
01/21/1967 217,000 Mar-67 #DIV/0!
01/28/1967 204,000 Apr-67 #DIV/0!
02/04/1967 216,000 May-67 #DIV/0!
02/11/1967 229,000 Jun-67 #DIV/0!
02/18/1967 229,000 Jul-67 #DIV/0!
02/25/1967 242,000 Aug-67 #DIV/0!
03/04/1967 310,000 Sep-67 #DIV/0!
03/11/1967 241,000 Oct-67 #DIV/0!
03/18/1967 245,000 Nov-67 #DIV/0!
03/25/1967 247,000 Dec-67 #DIV/0!
04/01/1967 259,000 Jan-68 #DIV/0!
04/08/1967 257,000 Feb-68 #DIV/0!
04/15/1967 299,000 Mar-68 #DIV/0!
04/22/1967 245,000 Apr-68 #DIV/0!
04/29/1967 255,000 May-68 #DIV/0!
05/06/1967 254,000 Jun-68 #DIV/0!
05/13/1967 231,000 Jul-68 #DIV/0!
05/20/1967 230,000 Aug-68 #DIV/0!
05/27/1967 228,000 Sep-68 #DIV/0!
06/03/1967 248,000 Oct-68 #DIV/0!
06/10/1967 238,000 Nov-68 #DIV/0!
06/17/1967 224,000 Dec-68 #DIV/0!


The "Average for the month" contains the averageifs function, asking for the criterion1 to be the same month as the third column, and the criterion2 being the same year as the third column. When I go through the Evaluate Formula process, the first cell shows month=1 and year=1967, but I am getting the #div/0! result. The spreadsheet is attached.

I believe I have the formulas right, but if someone can tell me why it isn't working, I will be most grateful.

[Glenn Kennedy]

Hi. You can use:

=AVERAGEIFS($B$4:$B$107,$A$4:$A$107,">"&EOMONTH(E4,-1),$A$4:$A$107,"<="&E4)

[Pepe Le Mokko]

Or perhaps a simple Pivot Table?

[jasman922]

Glen:

Fabulous! Thanks. May I ask your indulgence and walk me through the criteria you used

">"&EOMONTH(E4,-1)

and

"<="&E4

As you see I tried the simple MONTHVALUE method. What is "&EOMONTH"?

Jasman922

[Glenn Kennedy]

Your dates in E are all the last day of the month.

EOMONTH gives you the date of the last day of the month EOMONTH(date, No. of months) So the -1 gives you the date of the last day of the PREVIOUS month. Thus: ">"&EOMONTH(E4,-1) reads as greater than the date of the last day of the month PREVIOUS to the date in E4.

"<="&E4 reads as less than or equal to the date in E4.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Pepe Le Mokko]

Well I suppose that fancy formulas are more attractive than a PT