Current Averages of Year to Date

[Hugo Puflett]

Hi Everybody,

I am trying to find out the average output of my business for the most recent month and year to date. 

I was wondering how you might make this sort of rolling average. 

Currently I had gone for using the averageifs function but am not too sure about syntax.

If anyone has any advice, that would be greatly appreciated!

Best,

Hugo

[#2]

In English, your formula returns the average of total output for weeks ending from tomorrow to 360 days from today. Is that what you want to obtain from your formula?

[#3]

@Twifoo Thank you for the reply. Yes I would like to get the weekly average for the year
and then also for the month so I assume I would be just changing it to TODAY()-30

Trouble is I'm not actually getting anything

[#4]

I am actually getting #DIV/0

[#5]

Please attach your sample file so that I can personally test the formula and probably discover the reason for the #DIV/0! error.

[#6]

@Hugo Puflett 

Your formula is trying to return the projected total output for the next 360 days. To return the historical output for the last 1 month before today, your formula should be: 

=AVERAGEIFS(Table5[Total Output],

Table5[Week Ending],">="&EDATE(TODAY(),-1),

Table5[Week Ending],"<="&(TODAY())

For the last 1 year before today, your formula should be: 

=AVERAGEIFS(Table5[Total Output],

Table5[Week Ending],">="&EDATE(TODAY(),-12),

Table5[Week Ending],"<="&(TODAY())

Your current formula is returning #DIV/0! error probably because there are no dates in the Week Ending column of Table5, which are after today.