average with if and AND statements

[Guest]

Hi,

This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if
condition but I’m trying to add one more AND statement to it like this:
IF(F2:F190=B5) AND G2:G190=B5 than average K2:K190. I changed the above to
the next array formula: =AVERAGE(IF(AND(F2:F190=A5,G2:G190=B5),K2:K190)) but
it doesn’t work.
Does anybody know how to do this?

 

[Guest]

you only have one condition there,what happens if the average isnt equal to b5?
Any way this is what i would do,slightly different ranges,but
=IF(AND(AVERAGE(A1:A5)=A6,AVERAGE(C1:C5)=A6),AVERAGE(C1:C5),"averages NOT
eqaul A6")

 

[Guest]

Thank you for your answer Paul!
Sorry I have made a mistake in my question.
Please read IF(F2:F190=A5) not IF(F2:F190=B5). So i need to get the
average in column K2:K190 only for the cells which corresponding(offset)
cells in Column F2:F190=A5 AND Column G2:G190=B5.

 

[Max]

... average in column K2:K190 only for the cells which corresponding
cells in Column F2:F190=A5 AND Column G2:G190=B5 ..

Try, array-entered (CTRL+SHIFT+ENTER):
=AVERAGE(IF((F2:F190=A5)*(G2:G190=B5),K2:K190))

 

[Guest]

This one realy works.
Thank you Max!

Tim

Try, array-entered (CTRL+SHIFT+ENTER):
=AVERAGE(IF((F2:F190=A5)*(G2:G190=B5),K2:K190))

 

[Max]

You're welcome, Tim !