Help with IF(AND(AVERAGE...

**girlofscience** · 07-27-2009, 12:14 PM

Hello,

You have been incredibly helpful in the past. Can anyone find what is wrong with the function I am trying to use:

=IF(AND(Sorted!$B$2:Sorted!$B$38377=Pluses!A2,Sorted!$F$2:Sorted!$F$38377=Pluses!B2,Sorted!$L$2:Sort ed!$L$38377=Pluses!C2,Sorted!$M$2:Sorted!$M$38377=1,Sorted!$E$2:Sorted!$E$38377="plus",Sorted!$I$2:S orted!$I$38377="no"),AVERAGE(Sorted!$N$2:Sorted!$N$38377),"X")

I know that this is an array formula, and I have been using Shift+Ctrl+Enter. The formula itself does not cause an error, but I end up with the "X" in the cell, and that's not correct, because there are values that match all of these statements.

I am using two sheets called "Sorted" and "Pluses."

I am trying to do the following:

IF all of the following are true:
- the value in SortedColumnB for a particular row equals PlusesA2
- the value in SortedColumnF for a particular row equals PlusesB2
- the value in SortedColumnL for a particular row equals PlusesC2
- the value in SortedColumnM for a particular row equals 1
- the value in SortedColumnE for a particular row equals 'plus'
- the value in SortedColumnI for a particular row equals 'no'

THEN:
average all of the values in SortedColumnN for which cells B, F, L, M, E, and I for a particular row meet all of specified criteria

Thanks in advance!

**daddylonglegs** · 07-27-2009, 12:23 PM

Try like this

=AVERAGE(IF((Sorted!$B$2:$B$38377=Pluses!A2)*(Sorted!$F$2:$F$38377=Pluses!B2)*(Sorted!$L$2:$L$38377= Pluses!C2)*( Sorted!$M$2:$M$38377=1)*(Sorted!$E$2:$E$38377="plus")*(Sorted!$I$2:$I$38377="no"), Sorted!$N$2:$N$38377))

confirmed with CTRL+SHIFT+ENTER

**DonkeyOte** · 07-27-2009, 12:24 PM

Assuming you're running a version pre 2007:

Code:

=AVERAGE(IF((Sorted!$B$2:$B$38377=Pluses!A2)*(Sorted!$F$2:$F$38377=Pluses!B2)*(Sorted!$L$2:$L$38377=Pluses!C2)*(Sorted!$M$2:$M$38377=1)*(Sorted!$E$2:$E$38377="plus")*(Sorted!$I$2:$I$38377="no"),Sorted!$N$2:$N$38377))
committed with CTRL + SHIFT + ENTER

(edit: the above is a repetition of dll's formula)

If running XL2007 you should make use of the AVERAGEIFS function.

NOTE: the above array will be a poor performer given magnitude of ranges... pending volume of arrays to be put into use it might also be worth considering a Pivot Table perhaps ?

**girlofscience** · 07-27-2009, 12:39 PM

Thanks for your help. I tried that formula, and it's giving me a Div/0 error. I played around with it for a bit and can't find out why.

I've never used pivot tables before. I think I might try now. Thanks.

**DonkeyOte** · 07-27-2009, 12:44 PM

The #DIV/0 would imply you have no rows in your data that meet all of the specified criteria - try removing one test at a time from the formula (remembering to confirm with Ctrl + Shift + Enter each time) so as to see which conditions are met and which are not to help pinpoint the possible underlying issue.