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

Thread: Divide by Zero

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Surprise, AZ
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a worksheet:

    Column B is a value for year 2002
    Column C is a value for year 2001
    Column D is a percentage of increase or decrease between the two years.

    If both B & C are equal to zero I want
    zero in column D, otherwise I want the percent of increase or decrease versus year 2001. In no instance to I want a Divide by Zero error.

    thanks,

    [ This Message was edited by: hal1543 on 2002-05-02 06:54 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something with the following format:

    =IF(A1=B1,0,B1/A1)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Surprise, AZ
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Al,

    I tried your formula, but if A is greater
    than zero and B is = zero, I'm getting that
    dredded Divide by Zero error.

    Hal

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

    Give this a whirl:

    =IF(c1=0,0,b1/c1)

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-02 10:57 ]

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how about
    =if(or(b1=0,c1=0),0,b1/c1)
    this way, if either one is zero, you get a zero in return.

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

    Here's where we need to step back from Excel and remember our basic arithmetic. If B1 is zero and you divide this by a number greater than zero, the solution is always zero, no need to extend the if statement.

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

    Default

    Depending on what you divide by what, I'd use Nate's suggestion (a bit shortened):

    =IF(C1,B1/C1,0)

    or

    =IF(B1,C1/B1,0)

    Aladin

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
  •