#DIV/0! error in Average If Array function

**ren1104** · 03-28-2007, 11:46 AM

I have this array formula:
=AVERAGE(IF(Detailed!$O$3:$O$188=Summary!A13,Detailed!$AA$3:$AA$188,"")) where Column O represents months (Jan, Feb, Mar, etc) and column AA represent the Dollar amount that is associated with the month.
If the month doesn't have the dollar amount, my formula will return #DIV/0! error message. I tried Iserror and if function to remove the error message and not working.
Please help.
Thank you very much!

**BigBas** · 03-28-2007, 11:55 AM

Try this fix and let me know if it works:

=AVERAGE(IF((Detailed!$O$3:$O$188=Summary!A13)*(Detailed!$AA$3:$AA$188<>0)*(Detailed!AA$3:$AA$188),""))

CTRL+SHIFT+ENTER

**NBVC** · 03-28-2007, 11:56 AM

Try ammending to:

=AVERAGE(IF(Detailed!$AA$3:$AA$188<>"",IF(Detailed!$O$3:$O$188=Summary!A13,Detailed!$AA$3:$AA$188,"")))

OR

=AVERAGE(IF(Detailed!$AA$3:$AA$188>0,IF(Detailed!$O$3:$O$188=Summary!A13,Detailed!$AA$3:$AA$188,""))) If you want to exclude 0 values too.

Both re-confirmed with Ctrl+Shift+Enter....

**ren1104** · 03-28-2007, 12:37 PM

Thank you for the quick response, I tried those 3 formulas and none of them works, they keep returning the DIV error,
Here's the snapshot of my spreadsheet on the Detailed worksheet:
O AA
Jan 500
Jan 125
Feb 150
Feb 135.5
Feb 400
Feb 500
Apr 1500
Apr 25
Apr 33

As you can see the month of March is not on the list, that is when I get the DIV error when I try to average the dollar amount by month.
Thank you.

**NBVC** · 03-28-2007, 12:52 PM

Try

=IF(ISNUMBER(MATCH(Summary!A13,Detailed!$O$11:$O$188,0)),AVERAGE(IF(Detailed!$O$11:$O$188=Summary!A13,IF(Detailed!$AA$11:$AA$188<>"",Detailed!$AA$11:$AA$188))),"")

CSE Confirmed