Help with Averageifs and Or Statement

[aviationgeek]

Good Afternoon -

I am having a problem with averageifs and or combination. It is not working like I have expected it to, and I have not been able to find a solution to my problem on-line.

I am looking at the following table:

Bank	Runway	Date		Taxi Time
1	8R	10/10/2015		5
1	9	10/10/2015		8
1	9	10/10/2015		4
1	26L	10/10/2015		7
1	9	10/11/2015		4
2	9	10/10/2015		9
2	26L	10/11/2015		2

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

I want to find the average time time for a Bank Number 1, on 10/10/2015, and used either runway 9 or 8R. The issue I am having is that with the runway portion. I so far have =AVERAGEIFS(E2:E8,A2:A8,"=1",C2:C8,DATE(2015,10,10)), but I have not been able to figure out how to have both runway conditions in the formula.

Any help would be great.

Thank you

 

[Aladin Akyurek]

You need to switch to a defferent type of formula...

Control+shift+enter, not just enter:

=AVERAGE(IF($A$2:$A$8=1,IF(ISNUMBER(MATCH($B$2:$B$8,{"8R",9},0)),
    IF($C$2:$C$8=DATE(2015,10,10),$E$2:$E$8))))

 

[aviationgeek]

You need to switch to a defferent type of formula...

Control+shift+enter, not just enter:

=AVERAGE(IF($A$2:$A$8=1,IF(ISNUMBER(MATCH($B$2:$B$8,{"8R",9},0)),
    IF($C$2:$C$8=DATE(2015,10,10),$E$2:$E$8))))

I did use your code, but it is still not coming up with the correct answer. It is showing as 5.66666667. It is not counting the last Bank 1 flight. It is only averaging the first 3 flights and after it hits Runway 26L, it stops looking at the data. How can I fix this?

Thank you for your help.

 

[Aladin Akyurek]

I did use your code, but it is still not coming up with the correct answer. It is showing as 5.66666667. It is not counting the last Bank 1 flight. It is only averaging the first 3 flights and after it hits Runway 26L, it stops looking at the data. How can I fix this?

Thank you for your help.

Row\Col	A​	B​	C​	D​	E​
1​	Bank	Runway	Date		Taxi Time
2​	1	8R	10/10/2015		5
3​	1	9	10/10/2015		8
4​	1	9	10/10/2015		4
5​	1	26L	10/10/2015		7
6​	1	9	10/11/2015		4
7​	2	9	10/10/2015		9
8​	2	26L	10/11/2015		2

The conditions you mentioned:

(a) A2:A8 must equal 1;
(b) B2:B8 must match either 8R or 9;
(c) C2:C8 must equal DATE(2015,10,10).

This yields: 5.666667 as far as I can see.

<colgroup><col width="64" style="width:48pt"> <tbody>
</tbody>

 

[aviationgeek]

Yes it is. I did not look close enough at the date on that last flight. Thank you for your help

 

[Aladin Akyurek]

Yes it is. I did not look close enough at the date on that last flight. Thank you for your help

You are welcome. Thanks for the update.