Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: #DIV/0!

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

    Default

    Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ?

    Any help would be appreciated. Thanks

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

    Default

    On 2002-05-07 05:27, swartthydog wrote:
    Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ?

    Any help would be appreciated. Thanks
    =IF(B1,A1/B1,0)

    where the denomitor is first tested for being a non-zero number.

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this formula, just substitue A1 & B1 for the relevent cell refs or numbers.

    =IF(ISERROR(A1/B1),"0.00",A1/B1)

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Most probably
    List your formula for better help...

    =IF(A2=0,0,A1/A2)

    Tom

    How's that Mark?
    _________________
    Found a solution? If so, please post again so members of this board can spend their time helping others. Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved". Thanks for being courteous!

    [ This Message was edited by: TsTom on 2002-05-08 01:05 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 05:34, TsTom wrote:

    =IF(OR(A1=0,A2=0),0,A1/A2)

    ...If A1 = 0 or A2 = 0 then this formula returns 0.
    Why are you concerned with the value of A1?

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unless you are sure that only numbers will be put in A1 and A2 it is better to use the ISERROR formula. Otherwise you will get another error message. Eg if you put a number in A1 and Nil in A2 you get the #VALUE! error.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 08:40, inarbeth wrote:
    Unless you are sure that only numbers will be put in A1 and A2 it is better to use the ISERROR formula. Otherwise you will get another error message. Eg if you put a number in A1 and Nil in A2 you get the #VALUE! error.
    Not so... If A2 is empty and used in an expression it is treated as 0. It is inadvisable to hide or ignore the presence of an error using the ISERROR function.

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

    It is another matter if you want to be notified of some other error.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 08:50, inarbeth wrote:
    Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

    It is another matter if you want to be notified of some other error.
    I understand the "question" perfectly... and, Aladin's recommended solution...

    =IF(B1,A1/B1,0)

    ...is preferrable to all others.

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-07 08:56, Mark W. wrote:
    On 2002-05-07 08:50, inarbeth wrote:
    Sorry to be contrary Mark but the question was "Is there a way to return a "0" rather than the familiar #DIV/0! in a formula when you are in fact dividing by "0" ? "

    It is another matter if you want to be notified of some other error.
    I understand the "question" perfectly... and, Aladin's recommended solution...

    =IF(B1,A1/B1,0)

    ...is preferrable to all others.
    Aladin's formula returns an error message if text is put in A1 or B1. There may be good reasons to want an error message but the ISERROR formula is widely used and may be what swartthydog wants.

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
  •