'Excel Averageifs with both AND and OR
I would like to get the average of a column if some nested AND / OR conditions are met:
=AVERAGEIFS (Target_Col,(Col_A >= Date1 AND Col_B <= Date2 ) OR (Col_A >= Date3 AND Col_B <= Date4)
Is there a way to do it? Thanks
Solution 1:[1]
Probably the simplest way to handle this situation is to create a "helper" column in your workbook that contains the AND/OR functions, and then create an average of that column.
Example:
New column contains the formula IF(OR(AND(ColA >= Date1, ColB <=Date2), AND(ColA >= Date3, ColB <= Date4)),TargetCol,"")
This will return the value to average if conditions are met, or a blank if no conditions are met. Then simply take an average of your "helper" column.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Trevor Hughes |