OR - in nested IF - in AVERAGE

[jzgirl]

Hello.  I have three columns of data, A, B, and C.  What I would like to do is the following:

• For all values of A that =1 AND for all values of B that =9 OR =blank, calculate the average of the values in C
• This is a vector formula so I use <CTRL> <SHIFT> <ENTER>
• This is the formula I use:
• {AVERAGE(IF($A$10:$A$1048576=$E3,IF(OR($B$10:$B$1048576="",$B$10:$B$1048576=9),$C$10:$C$1048576)))}

But it is is returning a value that is not correct. I can't figure it out and I asked our resident Excel expert at work and he couldn't, either. Please help!!! Thank you!

[#2]

@jzgirl 

OR returns only a single TRUE/FALSE value, so if even one of the cells in column B contains 9 or is blank, the OR part will return TRUE. Use this instead (confirmed by pressing Ctrl+Shift+Enter):

=AVERAGE(IF(($A$10:$A$1048576=$E3)*(($B$10:$B$1048576="")+($B$10:$B$1048576=9)),$C$10:$C$1048576))

Alternatively, use a column with helper formulas, for example column D. In D10:

=AND(A10=$E3, OR(B10=9, B10=""))

Fill down.

The formula for the average then becomes (as an ordinary, non-array formula):

=AVERAGEIF($D$10:$D$1048576, TRUE, $C$10:$C$1048576)