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 val...
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)