Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Need help with #Divide By Zero error
I am trying to calculate the average duration taken to complete a project for a date range. I get a “ #Divide By Zero “ error and would like to use IERROR function to get 0 when divided by 0 instead of the error message. Please see the formula and the details below.
=SUMIFS(Duration:Duration, [Project Complete Date]:[Project Complete Date], >TODAY(-274), [Project Complete Date]:[Project Complete Date], <TODAY(-183)) / [Completed Project]20
Duration:Duration = Duration for each project
[Project Complete Date]:[Project Complete Date], >TODAY(-274), [Project Complete Date]:[Project Complete Date], <TODAY(-183) = Project date range
[Completed Project]20 = Number of Projects completed, number field start from Zero to above
Comments
-
Hello Anil,
Thanks for the question. If you want the formula to result in a 0 if there's an error, you can do so using the IFERROR() function (https://help.smartsheet.com/function/iferror). Here's an example of how this can be written:
=IFERROR(SUMIFS(Duration:Duration, [Project Complete Date]:[Project Complete Date], >TODAY(-274), [Project Complete Date]:[Project Complete Date], <TODAY(-183)) / [Completed Project]20, 0)
This will result in a 0 if there is any error however, and not just if there's a #DIVIDE BY ZERO error. If you'd like to only account for this, you can use an IF() statement at the begging of the formula to check what the "[Completed Project]20" cell's value is. Here's how that might look:
=IF([Completed Project]20 <> 0, SUMIFS(Duration:Duration, [Project Complete Date]:[Project Complete Date], >TODAY(-274), [Project Complete Date]:[Project Complete Date], <TODAY(-183)) / [Completed Project]20, 0)
This will show the result of your formula only if [Completed Project]20 isn't 0, otherwise if it's 0 or blank then it results in 0.
-
Thank you Robert!!! I used the 2nd option as that is a better way to handle this issue. Thanks for your suggestion too.
-
Hello,
Hoping you might be able to assist me with a similar issue I'm experiencing. I've attempted to import a Trello board into Smartsheet for one of my colleagues and I'm getting the same error message, when I view it in grid view on some of the rows.
The formula in question that is currently being used is: =COUNTIF(CHILDREN(), "=1") + "/" + COUNT(CHILDREN()) + " (" + ROUND(COUNTIF(CHILDREN(), "=1") / COUNT(CHILDREN()) * 100) + "%)"
For a bit of context, the formula being used is in a column known as "Complete" and it has checkboxes in but for the all the rows that say "Action / Status" in the "Task Name" column, the #Divide By Zero error message appears.
Please see the screenshot for an idea of what the grid view looks like.
Any ideas/suggestions would be appreciated!?
-
Hi,
The formula looks strange! I'd be happy to take a look!
Can you maybe share the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Have a fantastic week!
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives