CODEWITHSUNDEEP
excel
l3win
l3win

Reputation: 235

Excel - Averageifs and excluding data range according to dates

I am trying to average data where I exclude a certain date range (4/1/14 - 8/30/14) and also have additional criteria. I am trying to use averageifs for this. Here is my attempt, dates are in column C:

=AVERAGEIFS(D12:D8772,$P$12:$P$8772,">100",$C$12:$C$8772,"<4/1/14",$C$12:$C$8772,">8/30/14")

It doesn't work since there is a contradiction, I think. I am asking to include everything before 4/1/14 and everything after 8/30/14, which I think Excel looks to as a contradiction.

How can I modify my command to exclude the data range from 4/1/14 - 8/30/14?

Upvotes: 0

Views: 2894

Answers (2)

barry houdini
barry houdini

Reputation: 46331

In this scenario the simplest approach, avoiding too much repetition, is probably to use an "array formula" where you can use + to simulate "OR", i.e.

=AVERAGE(IF($P$12:$P$8772>100,IF(($C$12:$C$8772<DATE(2014,4,1))+($C$12:$C$8772>DATE(2014,8,30)),D12:D8772)))

confirmed with CTRL+SHIFT+ENTER

Note: you are including the last day of August - 8/31/2014, is that deliberate?

Upvotes: 1

Fernando Aires
Fernando Aires

Reputation: 531

AVERAGEIFS uses all criteria you offer to select a number. If I understood correctly, you are trying to choose dates before 4/1/14 OR after 8/30/14.

To do that you could calculate average by yourself. Do two SUMIFSs, as:

=SUMIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, "<4/1/14")
=SUMIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, ">8/30/14")

And sum them both, dividing by the sum of corresponding COUNTIFS, such as:

=(SUMIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, "<4/1/14")
  +SUMIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, ">8/30/14"))
 /
(COUNTIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, "<4/1/14")
 +COUNTIFS(D12:D8772, [... other criteria ...], $C$12:$C$8772, ">8/30/14"))

Hope that helps.

Upvotes: 1

Related Questions