Getting an average within a specific date range

[SoCal Mitch]

Hi All,


I'm new here and trying to solve a problem. I am trying to get an average for data in one column but only between specific date ranges.


IE: Column G has the date. Column I has the data I want the average for. There can be multiple entries for the same date, all on their own row.


I want to create a cell that shows me the averages of column I but only for the most recent 7 calendar days. I'd like to not have to continually edit the cell to specify the date range and simply let excel calculate the dates.


I have tried using the "averageifs" as follows:
=AVERAGEIFS(I:I,G:G,"<aa17",g:g,"ab17") work.
<aa17",g:g,">ab17") In this example I created cell aa17 to show the current date plus 1 and ab17 to show the current minus 6. This doesn't work.

I have tried using the "averageifs" also as follows:
=AVERAGEIFS(I:I,G:G,"now()+1",G:G,"now()-6") This doesn't work either. Also tried removing the quotes on the now statements without success.


Please, if anybody can help me with this, I'd appreciate it.

Mitch</aa17",g:g,"></aa17",g:g,"ab17")>

 

[StephenCrump]

Welcome to MrExcel!

Try:

=AVERAGEIFS(I:I,G:G,">="&AB17,G:G,"<="&AA17)

or

=AVERAGEIFS(I:I,G:G,">="&TODAY()-6,G:G,"<="&TODAY()+1)

but it would be better not to use whole column references.

 

[SoCal Mitch]

Welcome to MrExcel!

Try:

=AVERAGEIFS(I:I,G:G,">="&AB17,G:G,"<="&AA17)

or

=AVERAGEIFS(I:I,G:G,">="&TODAY()-6,G:G,"<="&TODAY()+1)

but it would be better not to use whole column references.

Thank you for your help. Your first option worked perfectly.