GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx

[BRIITO]

I am having problems gatting rid of the #div/o! In an excel sheet. It has all this formulas so that my employees don't have to do them manually and insted just enter info in a data_base. But i don´t know what to do when i get #div/o! In the following formula:

=averageifs(sheet1!$m$5:$m$2977;sheet1!$a$5:$a$2977;sheet2!c$40;sheet1!$p$5:$p$2977;sheet2!$b41).

I get the #div/o! When there is no data on sheet1 matching the value on sheet2!$b41, and they look awful...specially when i print and post the reports...

Please help!!!

 

[schielrn]

Try:

=iferror(averageifs(sheet1!$m$5:$m$2977;sheet1!$a$5:$a$2977;sheet2!c$40;sheet1!$p$5:$p$2977;sheet2!$b41),0)

Change the 0 to whatever you want to display.

Hope that helps.

 

[tony0682]

=IF(ISERROR(AVERAGEIFS(Sheet1!$A$5:$A$2977,Sheet1!$A$5:$A$2977,Sheet2!C$40,Sheet1!$A$5:$A$2977,Sheet2!$B41)),"",AVERAGEIFS(Sheet1!$A$5:$A$2977,Sheet1!$A$5:$A$2977,Sheet2!C$40,Sheet1!$A$5:$A$2977,Sheet2!$B41))

Shows a blank if DIV/0 shows

 

[jmthompson]

Try modifying your formula to be:

=if(iserror(your formula),"",(your formula))

 

[BRIITO]

Great!!!!!!
Thanks to you both (schielrn & tony0682) both solutions worked!!
Now i'll have a relaxed weekend!!!