Conditional Average (2 conditions)

K

kayard

I have in A1:A100 either numbers or #N/A. In B1:B100 there is eithe
TRUE or FALSE. I want to calculate the average of A1:A100 but only o
those rows where there is an actual number (Not #N/A) and wher
condition in column B is true.

I need this to be made in one formula without adding any more column i
the sheet! is it possible ?

Thanks

Paol
 
B

Bob Phillips

=AVERAGE(IF((NOT(ISNA(A1:A10)))*(B1:B10),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
M

Max

One way ..

Place in say, C1's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=AVERAGE(IF((ISNUMBER(A1:A100))*(B1:B100=TRUE),A1:A100))

Adapt the ranges to suit
 
A

Aladin Akyurek

=AVERAGE(IF(ISNUMBER(A1:A100),IF(B1:B100,A1:A100)))

which you need to confirm with control+shift+enter, not just with enter.
 
Top