average with if and AND statements
 

Hi,

This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if
condition but Im 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 doesnt work.
Does anybody know how to do this?

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),AVERA GE(C1:C5),"averages NOT
eqaul A6")
--
paul
remove nospam for email addy!



"Tim" wrote:

Hi,

This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if
condition but Im 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 doesnt work.
Does anybody know how to do this?

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.



"paul" wrote:

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),AVERA GE(C1:C5),"averages NOT
eqaul A6")
--
paul
remove nospam for email addy!



"Tim" wrote:

Hi,

This array formula =AVERAGE(IF(F2:F190=B5,K2:K190)) works great for one if
condition but Im 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 doesnt work.
Does anybody know how to do this?

"Tim" wrote:
... 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))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

This one realy works.
Thank you Max!

Tim



"Max" wrote:

"Tim" wrote:
... 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))
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

You're welcome, Tim !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Tim" wrote in message
...
This one realy works.
Thank you Max!

Tim