Results 1 to 8 of 8

getting rid of #DIV/o!

This is a discussion on getting rid of #DIV/o! within the Excel Questions forums, part of the Question Forums category; Is there a way to get rid of that #DIV/0! error? I have a workbook I use as a template ...

  1. #1
    New Member
    Join Date
    May 2003
    Location
    Massachusetts
    Posts
    14

    Default getting rid of #DIV/o!

    Is there a way to get rid of that #DIV/0! error? I have a workbook I use as a template for different projects, and it starts out blank each time. If I don't use a certain row in a certain project, then I have a column which reads #div/0!

    Example: =sum(g13/c13) c13=0, but a number will be filled in later.

    Please! Help!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default Re: getting rid of #DIV/o!

    One of:

    =IF(C13,G3/C3,"")

    =IF(C13,G3/C3,0)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: getting rid of #DIV/o!

    Quote Originally Posted by Randi_M
    Is there a way to get rid of that #DIV/0! error? I have a workbook I use as a template for different projects, and it starts out blank each time. If I don't use a certain row in a certain project, then I have a column which reads #div/0!

    Example: =sum(g13/c13) c13=0, but a number will be filled in later.

    Please! Help!
    Why are you summing a single value?

  4. #4
    New Member
    Join Date
    May 2003
    Location
    Massachusetts
    Posts
    14

    Default Re: getting rid of #DIV/o!

    What do you mean?

  5. #5
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: getting rid of #DIV/o!

    Your formula quoted was:

    =sum(g13/c13)

    G13/C13 is a number so there is no need for the SUM().

  6. #6
    Board Regular ddubnansky's Avatar
    Join Date
    May 2003
    Location
    USA - East Coast
    Posts
    74

    Default Re: getting rid of #DIV/o!

    I think this will give you the desired...

    Put the following in the corresponding cell reference (only the blue text):

    A1 - 0
    B1 - 1000
    C1 - =IF(ISERROR(B1/A1),"N/A",B1/A1)


    The "N/A" ref in the C1 formula will display a N/A for each #DIV/0! error message... If you wish it to say something different (text wise), type whatever you wish between the quotes in the formula... If you wish the cell to display nothing, just remove the N/A from the formula but LEAVE the quotes... The quotes must be left to display nothing (i.e =IF(ISERROR(B1/A1),"",B1/A1))... Also note if you wish a numeric value to be displayed or another formula to occur in the event this eror message happens, you must type that value/formula in the area of the formula that reads "N/A" (i.e =IF(ISERROR(B1/A1),sum(A1:B1),B1/A1))(yes, remove the quotes in this case - quotes are only used to denote that a text field will be placed in the cell)... Good Luck.

  7. #7
    New Member
    Join Date
    May 2003
    Location
    Massachusetts
    Posts
    14

    Default Re: getting rid of #DIV/o!

    Thanks, that works perfectly!!!

    Thanks again

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default Re: getting rid of #DIV/o!

    Quote Originally Posted by ddubnansky
    I think this will give you the desired...

    Put the following in the corresponding cell reference (only the blue text):

    A1 - 0
    B1 - 1000
    C1 - =IF(ISERROR(B1/A1),"N/A",B1/A1)


    The "N/A" ref in the C1 formula will display a N/A for each #DIV/0! error message... If you wish it to say something different (text wise), type whatever you wish between the quotes in the formula... If you wish the cell to display nothing, just remove the N/A from the formula but LEAVE the quotes... The quotes must be left to display nothing (i.e =IF(ISERROR(B1/A1),"",B1/A1))... Also note if you wish a numeric value to be displayed or another formula to occur in the event this eror message happens, you must type that value/formula in the area of the formula that reads "N/A" (i.e =IF(ISERROR(B1/A1),sum(A1:B1),B1/A1))(yes, remove the quotes in this case - quotes are only used to denote that a text field will be placed in the cell)... Good Luck.
    Your use of ISERROR is like swatting flies with a sledgehammer. See Aladin's recommendation above for a more "surgical" approach.

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