Forum Discussion
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!
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):
=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)
