IFERROR Formula for Dividing By 0

IFERROR Formula for Dividing By 0

Happy Friday All:

I am trying to replace IF Formula with IFERROR so I do not receive #DIVIDE BY ZERRO Error. However, I receive Incorrect when I use the IFERROR. Can anyone help?

Existing Formula:

=IF(AND(TODAY() >= [Start Date (Planned)]18, TODAY() <= [Finish Date (Planned)]18), ROUND(NETWORKDAYS([Start Date (Planned)]18, TODAY()) / Duration18, 2), IF(TODAY() > [Finish Date (Planned)]18, 1, 0))

 

Attempted Formula:

=IFERROR(AND(TODAY() >= [Start Date (Planned)]18, TODAY() <= [Finish Date (Planned)]18), ROUND(NETWORKDAYS([Start Date (Planned)]18, TODAY()) / Duration18, 2), IF(TODAY() > [Finish Date (Planned)]18, 1, 0))

 

Thanks

 

 

 

Tagged:

Comments

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You are missing the beginning IF statement. When using an IFERROR like this, you want to wrap the entire original formula.

     

    =IFERROR(original formula without the beginning =, output if error)

  • I try that and I received an error as well, Incorrect. I ma not sure I understand original formula without the beginning =, output if error

     

    =IFERROR(AND(TODAY() >= [Start Date (Planned)]11, TODAY() <= [Finish Date (Planned)]11), ROUND(NETWORKDAYS([Start Date (Planned)]11, TODAY()) / Duration11, 2), IF(TODAY() > [Finish Date (Planned)]11, 1, 0))

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Ray,

    It looks like you missed the IF function in the start.

    Try something like this.

    =IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]18, TODAY() <= [Finish Date (Planned)]18), ROUND(NETWORKDAYS([Start Date (Planned)]18, TODAY()) / Duration18, 2), IF(TODAY() > [Finish Date (Planned)]18, 1, 0)))

    Did it work?

    I hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Andree is correct. Basically that initial = is letting Smartsheet know that you are entering a formula.

     

    So you would start with the =.

     

    =

     

    Then begin your formula

     

    IFERROR(IF(AND(...............................

  • No, it responds with Incorrect

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Ok.

    Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])


     

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Taking a closer look at the formula, we are not actually filling in the second portion of the IFERROR statement.

     

    We have:

     

    =IFERROR(

     

                   IF(............, .............., IF(............., ..............., .............)))

    .

    We should have

     

    =IFERROR(

     

                   IF(............, .............., IF(............., ..............., .............))

           ...............)

    .

    Try this one...

     

    =IFERROR(IF(AND(TODAY() >= [Start Date (Planned)]18, TODAY() <= [Finish Date (Planned)]18), ROUND(NETWORKDAYS([Start Date (Planned)]18, TODAY()) / Duration18, 2), IF(TODAY() > [Finish Date (Planned)]18, 1)), 0)

  • Just shared with you

  • The results come back blank with this formula.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Thanks!

    I'll take a look and get back to you!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 11/20/19

    Did you get it working or do you still need help?

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    If the results came out blank, then it didn't meet any of your IF criteria.

     

    At least no you know your syntax is correct for the formula. Now you just need to tweak your IF statements to cover all of the possibilities you need to account for.

     

    I am not sure what your overall goal for the formula is, but based on the logic you have already built in, a Start Date in the past and a Finish Date in the Future will generate a blank.

     

    Now that we have your original request figured out, if you would like to provide more details as to how you want the formula as a whole to work, we may be able to help you out with that part as well.

  • Thanks for all the Help. I made the necessary corrections and everything worked perfectly. Thanks again!

     

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Excellent!

    Happy to help!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.