AVERAGEIFS Date is 30 days prior to N date, but not including N date

[actionpark]

Hi. Hoping someone can help me figure out this problem that I'm sure is so obvious that I'm going to be embarrassed when I learn the solution. I tried searching the forum and couldn't find a topic that's exactly the same.

I'm using AVERAGEIFS to find the mean PPM for a basketball team for the previous 30 days, not including the date in question.

This is my current formula:

=AVERAGEIFS(AWAY_PPM,AWAY_TEAM,TEAM_NAME,DATES,">"&DATE-30)

The issue I'm running into is that I only want the mean from 30 days BEFORE a date but not including it.

Here's a real life example, using the formula as such:

=AVERAGEIFS(AWAY_PPM,AWAY_TEAM,"PHI",DATES,">1/9/2019"-30)

The white box is my result using the above formula however it is incorrect for my use as I do not want the data FROM from 1/9/2019 in the average, only 30 days BEFORE that.

Thanks in advance for any help!

 

[James006]

Hi,

You could test the following :

=AVERAGEIFS(AWAY_PPM,AWAY_TEAM,"PHI",DATES,"<"&"1/9/2019",DATES,">="&"12/10/2018")

and you can replace "12/10/2018" by VALUE("1/9/2019")-30

Hope this will help