Rolling Average

[Joeclupis]

Hello all,

I have posted here before and you all have been really great in helping me out. I have a spreadsheet dashboard that I am trying to tweak. in one of the cells I have the following formula:
=AVERAGEIFS('DAILY VALUES'!I2:I5200,'DAILY VALUES'!A2:A5200,">="&MAX(IF('DAILY VALUES'!G2:G5200<>"",'DAILY VALUES'!A2:A5200)),'DAILY VALUES'!A2:A5200,"<="&TODAY())

Where A is the date column, I is a sales volume and G is a purchase column. What I would like to tweak is the number of days that we are using to get the average. I would like it to be the last 365 days, not the entire data set (over 8 years). I would also like to know if it is possible to change the 5200 number to an indeterminate number that would reflect the current date. I am afraid that someone who takes over for me would not know to change that number to a larger number in the future.

 

[FDibbins]

This will take you back 365 days (12 months)
EDATE(TODAY(),-12)

So your formula would be something like...
=AVERAGEIFS('DAILY VALUES'!I2:I5200,'DAILY VALUES'!A2:A5200,">="&MAX(IF('DAILY VALUES'!G2:G5200<>"",'DAILY VALUES'!A2:A5200)),'DAILY VALUES'!A2:A5200,">="&EDATE(TODAY(),-12))

you could increase the 5200 right now if you wanted to, to say, 10 000

 

[Joeclupis]

Thank you, just what we needed. This gives us a more accurate reading on what was sold in the last year. Am I correct in saying that the "5200 vs 10000" is just where fhe function should look for the information, and has no actual bearing on the average?

Joe