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.
This is a discussion on GETTING RID OF #DIV/0! IN AN AVERAGEIF Fx within the Excel Questions forums, part of the Question Forums category; ...
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!!!
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.
Always make a back up copy before trying new code, you never know what you might lose!
- Posting guidelines, forum rules and terms of use
-Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
"The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"
=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
Try modifying your formula to be:
=if(iserror(your formula),"",(your formula))
Great!!!!!!
Thanks to you both (schielrn & tony0682) both solutions worked!!
Now i'll have a relaxed weekend!!!
Like this thread? Share it with others