Average IF, AND formula question

[Harvey Waxman]

I'd like to average a column depending on two other column criteria:

I want the average of column H if criteria in M and C are met
=AVERAGE(IF(AND(M18:M11000<>0%,C18:C11000="R"),H18:H11000))

Array entry results in 0. Where is my error?

Thanks

 

[Carl Witthoft]

I'd like to average a column depending on two other column criteria:

I want the average of column H if criteria in M and C are met
=AVERAGE(IF(AND(M18:M11000<>0%,C18:C11000="R"),H18:H11000))

Array entry results in 0. Where is my error?

Thanks

So far as I've been able to determine in the past, Excel is too stupid
to be able to understand array formulas of that complexity. Some Excel
functions won't deal with being put into an array formula at all!

You can do what you want in a couple extra steps, viz. column J full
of =IF(AND(M1 <>0, C1="R",H1,"no") (fill down) and then averaging that
column, but taking care to divide by a COUNTIF(J1<>"no") .

As I often point out, if you want to do stuff like this, switch to R
or MatLab.

I'd also point out that, if you strictly want data where column M
equals zero, why not do

IF(C18:c1000="R",M18:M1000*H18:H1000,0) or something similar?

 

[Carl Witthoft]

Something else I forgot to mention:

since something like (M1:M10<>0) actually returns a vector of TRUE
and FALSE values, you can write all sorts of logical combinations using
"*" ..

This might work for your case:

=average((M18:M11000<>0%)*(C18:C11000="R")*(H18:H11000)) as an array
formula.

I just happen (personal bias) to think this is a very non-intuitive way
to do things inside Excel. It's actually quite common in the
alternative tools I suggested previously.

 

[CyberTaz]

Have you had a look at:
=AVERAGEIF()
or, more probably in this case,
=AVERGEIFS()
?

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Harvey Waxman]

This might work for your case:

=average((M18:M11000<>0%)*(C18:C11000="R")*(H18:H11000)) as an array
formula.

I just happen (personal bias) to think this is a very non-intuitive way
to do things inside Excel. It's actually quite common in the
alternative tools I suggested previously.

This is what I finally used. {=AVERAGE(IF((M18:M11000>0%)*(C18:C11000="r"),H18:H11000))}

I don't see how your formula would average column H.

I agree that Excel sometimes seems to make things very obscure.

Many thanks. I'll look at your other suggestions.