# IFERROR Formula for Dividing By 0

Options
✭✭✭✭✭
edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

=

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

• ✭✭✭✭✭✭
Options

No, it responds with Incorrect

• ✭✭✭✭✭✭
Options

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)

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

Just shared with you

• ✭✭✭✭✭✭
Options

The results come back blank with this formula.

• ✭✭✭✭✭✭
Options

Thanks!

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
edited 11/20/19
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!