Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

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
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-01-14 16:20, Patrick wrote:
    Can anyone tell me why this insists on bringing up the #DIV/0! Error
    =IF((E6-F6)/E6="0","",(E6-F6)/E6)
    Egads, I'm surprised no one nailed this down already.

    Patrick, you probably already solved this, but in case you did not, here goes.

    Look at your IF "condition":
    (E6-F6)/E6="0"

    The only times this condition will be TRUE is when E6 and F6 are EQUAL. Then your equation collapses to (0/E6); any time you devide zero by something you get zero.

    A better test for your purposes is:

    E6=0

    because what you REALLY want to avoid with this IF equation is having to divide by zero, which is undefined and thus causes computers to have hissy fits.

    So, your IF formula should read:
    =IF(E6=0,"",(E6-F6)/E6)

    which allows the results to be computed and displayed, and instead of getting DIV/0 errors, you get blanks whenever E6 happens to be 0.



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe this:

    =If(I7,G7/I7-1,0)

    change cell refs to suit

    James

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    I'm surprised no one nailed this down already.

    Do a Search on this poster's name, you'll understand.

    Aladin

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    San Ramon CA
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    let me guess, he's the type that posts repeatedly until someone gets on it.

    whereas I'm the one that skips randomly through the archives in search of easy questions...

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-01-14 16:20, Patrick wrote:
    Can anyone tell me why this insists on bringing up the #DIV/0! Error
    =IF((E6-F6)/E6="0","",(E6-F6)/E6)
    perhaps this would help:

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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using one of Aladin's tricks (This was a good one Al):

    =if(e6,(e6-f6)/e6,o)

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-06 18:02 ]

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
  •