Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: #DIV/0!

  1. #11
    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 09:25, inarbeth wrote:
    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.
    Why would anyone perform arithmetic operations on text such as "cat" and "dog"? What is "cat"/"dog"? Kibble?

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

    Default

    I did not mention cat or dog, so please don't patronise me. I don't know why you are so opposed to the ISERROR formula: it is one way of avoiding an error message. You say tomato and I say tomato. John Walkenbach's Excel 2000 Bible suggests it at page 221 so it has a decent provenance.

  3. #13
    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 13:50, inarbeth wrote:
    I did not mention cat or dog, so please don't patronise me. I don't know why you are so opposed to the ISERROR formula: it is one way of avoiding an error message. You say tomato and I say tomato. John Walkenbach's Excel 2000 Bible suggests it at page 221 so it has a decent provenance.
    Sorry, if you feel patronized... I have an innate aversion to bad advice!

    I believe in addressing the root cause of a problem... not simply attending to its symptoms. The original request was to "fix" #DIV/0! error. That is best addressed by testing the denominator (only) using the formula...

    =IF(B1,A1/B1,0)

    You seem intent on addressing an unrelated data type issue. If there's a possibility of using an arithmetic operator on entered text values then it's best to use Data Validation to prevent their entry. An ounce of prevention is worth a pound of cure. And, speaking of pounds -- ISERROR is a 2,000 pound (907.184619497623 kg) gorilla. If you check the Excel Help Topic for this function you'll see that it returns TRUE for EVERY error value...

    #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!

    So when you use...

    =IF(ISERROR(expression),0,expression)

    ...you lose all visiblity to underlying problems... it's like prying your eyes out!!! ...And, is very poor worksheet design regardless of whose Bible you quote.

    [ This Message was edited by: Mark W. on 2002-05-07 15:18 ]

  4. #14
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree with mark the original question did not ask what if I wanted to divide by text as well who does that not me!?

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

    Default

    I shall now retire from this thread having been p***ed on from a great height.


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

    Default

    Easy guys - I though the idea of these open forum type message boards was to pass on some of our experience / knowledge to other Excel users.

    Personally I can see benefits from both formulas. I guess it's up to the person who who asked for help to decide which is most suitable for their needs. And if nothing else, someone else reading the messages may be able to use some of the alternates offered.

    Don't sweat the small stuff. Enjoy the rest of your day

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

    On 2002-05-08 00:27, inarbeth wrote:
    I shall now retire from this thread having been p***ed on from a great height.

    Ian,

    that's one way of looking at it !

    another way, maybe, is that you've just been privvy to some excellent alternative one-to-one advice and help, at your own leisure : the sort of advice that would cost you 100 per hour if you did it privately

    and he's around to stay, with his main incentive being sharing his expert knowledge, not furthering his own ego by pissing on people like us

    he hasn't pissed on you from a great height, he's just nudged you and pointed you in the new direction of an open Excel door you may not have spotted earlier...

    Chris

  8. #18
    New Member
    Join Date
    May 2007
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #DIV/0!

    Good afternoon those that are more advanced than I! What is the solution to get the following formula to show 9% instead of 91%?

    =IF(ISERROR(1-(F54/H54)),0,F54/H54)

    Thank you in advance for your help!
    Last edited by billtester; Dec 17th, 2010 at 01:34 PM.

  9. #19
    Board Regular sous2817's Avatar
    Join Date
    Feb 2008
    Location
    Raleigh, NC
    Posts
    2,276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #DIV/0!

    One solution would be to put a 9 in F54 and a 100 in H54.

    I'm sure there are others, but if you could provide a bit more information and a bit more context, you'll probably get a better answer.
    "Even a blind pig finds an acorn now and again"

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    - Search Mr. Excel using Google

    - Search Mr. Excel using Bing

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
  •