OR - in nested IF - in AVERAGE

Copper Contributor

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!

1 Reply


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):




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)