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:

[TABLE="width: 304"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]Bank[/TD]
[TD]Runway[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Taxi Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8R[/TD]
[TD]10/10/2015[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]10/10/2015[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]10/10/2015[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]26L[/TD]
[TD]10/10/2015[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9[/TD]
[TD]10/11/2015[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]9[/TD]
[TD]10/10/2015[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]26L[/TD]
[TD]10/11/2015[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


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.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td] Bank[/td][td] Runway[/td][td] Date[/td][td] [/td][td] Taxi Time[/td][/tr]


[tr][td]

2​

[/td][td] 1[/td][td] 8R[/td][td] 10/10/2015[/td][td] [/td][td] 5[/td][/tr]


[tr][td]

3​

[/td][td] 1[/td][td] 9[/td][td] 10/10/2015[/td][td] [/td][td] 8[/td][/tr]


[tr][td]

4​

[/td][td] 1[/td][td] 9[/td][td] 10/10/2015[/td][td] [/td][td] 4[/td][/tr]


[tr][td]

5​

[/td][td] 1[/td][td] 26L[/td][td] 10/10/2015[/td][td] [/td][td] 7[/td][/tr]


[tr][td]

6​

[/td][td] 1[/td][td] 9[/td][td] 10/11/2015[/td][td] [/td][td] 4[/td][/tr]


[tr][td]

7​

[/td][td] 2[/td][td] 9[/td][td] 10/10/2015[/td][td] [/td][td] 9[/td][/tr]


[tr][td]

8​

[/td][td] 2[/td][td] 26L[/td][td] 10/11/2015[/td][td] [/td][td] 2[/td][/tr]
[/table]

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.[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.