Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: #DIV/0! Error

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me why this insists on bringing up the #DIV/0! Error
    =IF((E6-F6)/E6="0","",(E6-F6)/E6)

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    "IF((E6-F6)/E6="0","",(E6-F6)/E6)"

    most probably E6 has 0 or no value. Try checking the value of E6 instead of checking the whole formula in the if condition.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    In addition to what Shalini noted:

    Any number including 0 divided by 0 is undefined and Excel renders this as #DIV/0!

    An empty cell in math expressions interpreted by Excel as 0.

    Rewrite your formula as:

    =IF(E6,(E6-F6)/E6,"")

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    your first arguement that says if it's equal to "0" is telling it to recognise a textual value of "0"

    even if your (E6-f6/e6) is zero, it's not recognising it as text, and thusly defaulting to your 2nd arguement, which results in a division by zero....

    drop the "quotes" or go with Aladin's more compact solution


    :: Pharma Z - Family drugstore ::

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-26 12:26, Chris Davison wrote:
    your first arguement that says if it's equal to "0" is telling it to recognise a textual value of "0"

    even if your (E6-f6/e6) is zero, it's not recognising it as text, and thusly defaulting to your 2nd arguement, which results in a division by zero....

    drop the "quotes" or go with Aladin's more compact solution
    Guess what: Dropping the quotes won't help when E6=0 or empty .


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guess what : I posted without testing it first.

    Naughty Chris

Some videos you may like

User Tag List

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
  •