[SOLVED] Averageifs returning #DIV/0 error

Hi,
I have a formula that is looking to average percents of standard margins. The fields it looks up could possibly return "nothing" if that work cell is not being used that month.
my issue is how to get around the DIV/0 issue.
Another question- is there a "cleaner" method of doing this code, array perhaps?
The formula is as follows:.

Please Login or Register  to view this content.

Thanks in advance for any help!
Kevin

There are a number of ways to remove the #DIV/0 issue. Possibly the easiest to to say =if(Sheet2!CZ=0,"",AVERAGE(AVERAGEIFS(Sheet2!CZ........, or perhaps =IFERROR(Sheet2!CZ,0,AVERAGE(AVERAGEIFS(Sheet2!CZ......)

Without seeing the actual file to understand exactly which cells you're referencing, it's hard to say. Unfortunately I'm about to set off on a long journey so can't help any further, but please post at least a sample file...there are many good and helpful souls here that will help...good luck

Hello Kevin,

I'm not sure your formula does what you intend it to do. Firstly the AVERAGE function at the start is redundant because it's only averaging a single value, you can take that out and get the same result.

....also you are effectively "averaging averages" which isn't usually a good idea, by adding all the AVERAGEIFS functions and dividing by 11 you are effectively giving the same "weight" to 100 instances of PG2 as one instance of PG5. If you want an average of column CZ when column D = "2014/10" and column A = any of the other 11 listed values then this formula will do that (and you shouldn't have any problems with #DIV/0! errors)

=IFERROR(SUM(SUMIFS(Sheet2!CZ:CZ,Sheet2!D:D,"2014/10",Sheet2!A:A,{"PG2","PG5","PG7","PG9","assy","osp","#N/A","o/s","0","csp","PG2/PG5"}))/SUM(COUNTIFS(Sheet2!D:D,"2014/10",Sheet2!A:A,{"PG2","PG5","PG7","PG9","assy","osp","#N/A","o/s","0","csp","PG2/PG5"})),0)