CODEWITHSUNDEEP
excel-formulasumifs
Ryan Chase
Ryan Chase

Reputation: 2464

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

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

Upvotes: 5

Views: 43364

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

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

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

Upvotes: 1

Marcel
Marcel

Reputation: 2794

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

Upvotes: 7

Related Questions