0

I have a sheet that contains an amount in column D and the week number(A), month number (B), and year(C). How do I get the average of the last month's amount (D)? So in the example below, I am looking for the average of D251 to D254 based on last month (Column B) 7. Then next month, with it's September, The same cell would average all the "Amounts" in column D for the month of last month, Aug 8th. I hope that all makes sense.

How do I get the average of the last month's amount (D)?

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
Terry
  • 27
  • 4

2 Answers2

0

You can use AverageIF, or possibly AverageIFs (plural) to account for prior year. I updated my example. You also might consider a different structure/column for time that combines Year_Month. See example in sheet.

Formula used:

=IFERROR(AVERAGEIFS(D:D,B:B,if(B2=1,12,B2-1),C:C,if(B2=1,C2-1,C2)),"First Period")

See this example.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Sorry, I think AverageIFS (plural) is needed to account for change in years. You also might think of combining month and year. – pgSystemTester Aug 11 '21 at 16:13
  • Oh nice! I'm checking out the sheet now. Could I use something like =TEXT(EDATE(H2,-1),"MMMM YYYY") to reference the time? H2 would be =TEXT(TODAY(),"MM/YYYY") – Terry Aug 11 '21 at 16:15
  • That's it! Thanks! – Terry Aug 11 '21 at 16:18
0

Please try the following

=AVERAGE(INDEX(FILTER(A2:D,B2:B=MAX(B2:B)),,4))
marikamitsos
  • 10,264
  • 20
  • 26