Results 1 to 5 of 5

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

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

  1. #1
    New Member
    Join Date
    Oct 2008
    Location
    Mexico
    Posts
    19

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

    Please help!!!

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,923

    Default 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.
    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!"

  3. #3
    Board Regular
    Join Date
    May 2005
    Location
    New York
    Posts
    149

    Default 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. #4
    Board Regular
    Join Date
    Mar 2008
    Location
    Pennsylvania
    Posts
    964

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

    Try modifying your formula to be:

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

  5. #5
    New Member
    Join Date
    Oct 2008
    Location
    Mexico
    Posts
    19

    Default 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!!!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com