Results 1 to 3 of 3

Thread: replace #DIV/0!

  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    replace #DIV/0!

    Is there a way to replace "#DIV/0!" with '-' or 'nil' in cell as resulted some formula?

    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: replace #DIV/0!

    Say you have a formula =A1/A2. This returns #DIV/0 if A2 = 0. You can test whether A2 = 0:
    <code>
    =IF(A2=0,"-",A1/A2)
    </code>
    A more general solution goes like this: say you have a formula
    <code>
    =F
    </code>
    where F is some expression that may return an error value such as #ERROR or #DIV/0. Change the formula to
    <code>
    =IF(ISERROR(F),R,F)
    </code>
    where R is the value you want to return instead of an error. R may be a string, or a number, or an expression. In the above example:
    <code>
    =IF(ISERROR(A1/A2),"-",A1/A2)
    </code>
    This doesn't check specifically whether A2=0, but whether A1/A2 returns an error.

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: replace #DIV/0!

    thanks a bunch!
    -indra-

Posting Permissions

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