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

Thanks:  0
Likes:  0

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

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

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...

2. ## Re: GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx

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.

3. ## Re: GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx

=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

4. ## Re: GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx

Try modifying your formula to be:

5. ## Re: GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•