5

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???

Ryan Chase
  • 2,384
  • 4
  • 24
  • 33

2 Answers2

7

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:
enter image description here

Marcel
  • 2,764
  • 1
  • 24
  • 40
1

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

=AVERAGEIFS($1:$1,$2:$2,">="&E5,$2:$2,"<="&F5)
Dave Sexton
  • 10,768
  • 3
  • 42
  • 56