IFERROR Formula for Dividing By 0

Tony Barthelemy

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

 

 

 

Paul 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)

Anthony Barthelemy

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å

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

Paul 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(...............................

Anthony Barthelemy

No, it responds with Incorrect

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, andree@getdone.se)

 

Paul 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)

Anthony Barthelemy

Just shared with you

Anthony Barthelemy

The results come back blank with this formula.

Andrée Starå

Thanks!

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

Andrée Starå

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

Paul 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.

Anthony Barthelemy

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

 

Paul Newcome

Happy to help! 

Andrée Starå

Excellent!

Happy to help!