# OR - in nested IF - in AVERAGE

Copper Contributor

# OR - in nested IF - in AVERAGE

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!

# Re: OR - in nested IF - in AVERAGE

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)