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
Comments
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
-
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.
-
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(...............................
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No, it responds with Incorrect
-
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.
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Just shared with you
-
The results come back blank with this formula.
-
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.
-
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for all the Help. I made the necessary corrections and everything worked perfectly. Thanks again!
-
Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!