Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: help with DIV/O error

    Hi,

    I am having a hard time figuring out how to get my
    formula to work without getting this error (DIV/0)

    what I am trying to do is check a number in several cells
    and then either display "PD" or the %of change - the
    problem I have is once one of the cells goes to zero I
    get the div/o error - what I want it do display is just
    0% change.

    so in my example formula below (in cell E30) needs to
    check:

    If Cell D30 >=2.0 or Cell D28=0 AND E28>0 then
    display "PD"

    If Cell D30="PD" and Cell E28>0 then display "PD"

    that part seems to work fine - (and there may be an
    easier way to do it - I am not sure) - the problem I run
    into is when I run the next section of my formula:

    I need it to check the value in Cell E28 subtract that
    from the minumum value in the previous cells starting
    with C28 (and in this example through D28) and then
    divide it by the minmum number again (I am tracking a
    nadir value). But what happens is that once the previous
    cell (in this example d28) is 0 then I get the error. I
    can't figure out a way to either prevent the error from
    showing up or displaying 0%. Please help.

    I have tried the below formulas without any success:

    =IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD",IF(AND
    (D30="PD",E28>0),"PD",IF(E28=D28,"",(E28-MIN
    ($C$2828))/MIN($C$2828)))) - this gives a blank in
    cell E30 but a "PD" in cell f30

    =IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD",IF(AND
    (D30="PD",E28>0),"PD",IF(E28=D28,e30=0,(E28-MIN
    ($C$2828))/MIN($C$2828)))) - this gives a circular
    reference but enters a 0 in cell e30 but then a zero in
    all the following cells

    Any suggestions? Thanks in advance.

  2. #2
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    I can't figure out a way to either prevent the error from
    showing up or displaying 0%. Please help.
    With this part, here's one that many may/will frown upon, BUT if you've got your formulas debugged, you can use =IF(ISERROR(Formula),"",Formula)) or =IF(ISERROR(Formula),"0%",Formula))

    Note that ISERROR will prevent ALL error messages, not just DIV/0 and force your formulas to calculate 2x (in case processing speed is an issue).

    You might want to look at a SUMPRODUCT alternative. Shades is good at those; I suck at 'em.

    Hope that helps,

    Smitty

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    Hi Smitty,

    I entered the following and get an error in the formula - I am not sure what is wrong:

    =if(iserror(IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)), "PD",IF(AND(D30="PD",E28>0),"PD",(E28-MIN($C$2828))/MIN($C$2828))),0,IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD" ,IF(AND(D30="PD",E28>0),"PD",(E28-MIN($C$2828))/MIN($C$2828)))

    the error is indicated at the 0 value between the two formulas.

    Please help.

    Thanks.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Try this formula for your main formula:

    =IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD",IF(AND(D30= "PD",E28>0),"PD",(E28-MIN($C$28: D28))/MIN($C$28: D28)))

    (now for display purposes because of the smilies, I added a space between : and D )
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, here is you full formula. You were lacking a close parenthesis in the first part of the IF statement, which is why the 0 would cause an error.

    =IF(ISERROR(IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD", IF(AND(D30="PD",E28>0),"PD",(E28-MIN($C$28: D28))/MIN($C$28: D28)))),0,IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD",IF (AND(D30="PD",E28>0),"PD",(E28-MIN($C$28: D28))/MIN($C$28: D28))))

    Again, for display purposes, there is added a space between : and D, to prevent the smilies. So if, you copy this, be sure to take out those extra spaces.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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