Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding divide by zero errors (97 sr-2)

    <img src=/S/help.gif border=0 alt=help width=23 height=15>
    I am using the formula to generate a percentage in E7.
    <hr>=IF(C7>0,IF(D7>0,D7/C7,""),"")<hr>
    The nested IFs protect me from divide by zero errors if either C7 or D7 are zero (or a negative number - which I need to trap as well). This isn't elegant, bit of kludge actually. I'm not that familiar with Excel - is there an easier and/or tidier way? <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Just slightly more elegant:

    =IF(AND(C7>0,D7>0),D7/C7,"")

    Do you also need to trap text values in C7 or D7? In that case, try

    =IF(AND(C7>0,D7>0,ISNUMBER(C7),ISNUMBER(D7)),D7/C7,"")

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Hans <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Thanks for this, I hadn't considered trapping text as well but it is a (very) good idea! From the context it appears to me that the AND function evaluates any number of IF type tests and will execute the first formula if all are true. If one is not true it executes the second. - <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15> Is this right?
    Many thanks and have a good weekend!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Hi Les,

    The AND function itself doesn't evaluate the division (or nothing).

    The AND function merely evaluates a series of conditions and returns TRUE if all of them evaluate to TRUE. It returns FALSE if at least one of the conditions evaluates to FALSE.

    The IF function takes the first argument - in this case the result of AND(...) - and evaluates the second argument (the division) if the first argument evaluates to TRUE, otherwise evaluates the third argument (an empty string).

    Have a nice weekend too.

    Regards,
    Hans

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    =IF(C7*D7>0,D7/C7,"")

    The >0 bit is there, because you want to exclude negative numbers.

    Aladin
    Microsoft MVP - Excel

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    This can even be in the form

    =IF(C7,D7/C7,)

    if negative numbers are OK. Zero value numerators do not cause divide-by-zero errors.

    A spreadsheet author would need to have appropriate reasons to ignore negative numbers.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Doesn't work if both C7 and D7 are negative though.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    But it does...
    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    The >0 bit is meant to meet a condition the OP mentions. If negs are allowed,

    =IF(C7,D7/C7,0)

    will of course suffice.

    The reason why you added the "Zero value numerators do not cause divide-by-zero errors" to your reply escapes me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Aladin
    Microsoft MVP - Excel

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Aladin, I was replying to several parts of the thread, not just your response.

    I'm not sure if you are teasing me <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>, but in Excel, zero value numerators do not cause divide-by-zero errors, they return a zero value, where zero value denominators -do- cause divide-by-zero errors. I think you know that, but my point was that in some of the above solutions the reference to numerator D7 in the logical test argument of the =IF(,,) function was unnecessary.

    And just to tease you back:

    =IF(C7,D7/C7,)

    is the same as

    =IF(C7,D7/C7,0)

    I'm such a bad typist, I try to use as few characters as possible! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    On my Excel 2000, if I set C7 = -2 and D7 = -1, then your formula produces 0.5 not blank as the question indicated was required, and as the other solutions provide.
    Legare Coleman

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Legare,

    Although not fully applicable here for one reason, how could one forget Rory's star posting of last November (#87625) of the IFERROR function, which directly speaks to the issue of elegance.

    Unfortunately, IFERROR is only concerned with whether Excel considers the formula passed to it as being in error. If the user considers certain conditions as being in error (like negative numerator or denominator), IFERROR won't catch these. IFERROR doesn't provide a way to parse the argument (ie, the formula) passed to it to see if anything violates user conditions or to examine cell entries that are part of the formula. Hmmm, too bad.

    Fred

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    Now I understand why that terse "it doesn't work" comes from!

    I interpreted the OP's question/request as a combination of "avoid #DIV/0! " and "a negative % as result." I should have added the interpretation the formula instantiates.

    Aladin
    Microsoft MVP - Excel

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    I didn't mean my response to be terse, I was just trying to point out that problem.
    Legare Coleman

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding divide by zero errors (97 sr-2)

    I want to thank all of you here <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    I've learned a great deal about excel from your responses.

    have one on me! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •