Average a range of values if the date associated with it falls within a date range

Question

I'm trying to average a range of values if its associated date falls between two specified dates. The following function works:

AVERAGEIFS($1:$1,$2:$2,">=1/1/2014",$2:$2,"<=1/2/2014")

...in this case the values that you want to average are in row 1 and the dates that are associated with it are in row 2.

However, in this case I'm explicitly stating the date range in my formula (">=1/1/2014" and "<=1/2/2014"). Is there any way to create a similar formula that allows me to reference date cells to determine my date range instead of having to explicitly state the dates in the formula itself???

Answer 1

you can use the following, by putting the start date and the end date in another cell and refer to them in your formula:

=AVERAGEIFS($1:$1,$2:$2,">="&$B$4,$2:$2,"<="&$B$5)

with the following example:

Answer 2

Assuming your two dates are in E5 and F5 you could do it like this:

=AVERAGEIFS($1:$1,$2:$2,">="&E5,$2:$2,"<="&F5)