#DIVIDE BY ZERO issue

Options

Hi All,

Getting a #DIVIDE BY ZERO issue. I have two fields that are both 0% ... Hence #DIVIDE BY ZERO issue. Any thoughts to the below equation? I can't get it to work.

NOTE: When I use the IFERROR section only (IFERROR([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "") it works, the field is blank or whatever I put in between the quotations. Thats part is good....It's when I add the other lines I get the #DIVIDE BY ZERO issue.


=IFERROR([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row >= 0.75, "Yes", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <= 0.25, "No", "Hold")))


Results should be.....

IFERROR the field should be blank

IF Yes the field populates Green Circle

IF No the field populates Red Stop Sign

IF anything else field populates Yellow Triangle


Thanks for the help.

Answers

  • Kratt9
    Options

    My apologies...It was a DIVIDE BY ZERO issue but now it is an #INCORRECT ARGUMENT issue.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You need to specify what to do when there is an error. Looks like you may have forgotten that last piece of the IFERROR function when adding in the other IFs.

  • Kratt9
    Options

    Hi Paul,

    I am new to this... I am trying what you suggested but not doing it correctly. I guess I don't understand the concept or formula.

    Can you show me what you are thinking in the code?

    =IFERROR([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row >= 0.75, "Yes", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <= 0.25, "No", "Hold")))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Here is the one you said was working. The bold portion is the IFERROR portion.

    IFERROR([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "")


    When you created your nested IF statement, you forgot to include the last section of the IFERROR.

    =IFERROR(IF(.........., IF(.........., IF(..........))), "")

  • Kratt9
    Options

    Thanks Paul that worked for the divide by zero issue but, the Yes statement is not turning the complete indicators green now...The field will turn red... but green is not working, it is blank.

    Almost there....What do I need to correct to get the green indicator back? Thanks for your help so far.

    =IFERROR(IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row >= 0.75, "Yes", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <= 0.25, "No", "Hold"))), "")


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    The issue there is coming from your first IF.

    =IFERROR(IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <> 0, "", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row >= 0.75, "Yes", IF([% Construction Complete]@row / [% Elapsed Time to Baseline Duration]@row <= 0.25, "No", "Hold"))), "")


    If the outcome is not zero, then output blank.


    The reason Red is popping up is because 0 divided by 0.11 is equal to 0 which makes that first one false and allows the formula to move on to the rest of the IFs. I would suggest getting rid of that one altogether (as well as the corresponding closing parenthesis closing out the nested IF in bold there towards the end).

  • Kratt9
    Options

    Thanks Paul.... I got it now. I appreciate your help. I think there is something off on the formulas but will continue to work those. Have a great weekend!

  • Kratt9
    Options

    Just FYI everything is now working as it should. Thank you so much. This community is awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!