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

Find more posts tagged with

IT & Operations

Comments

Robert S.

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.

Anil

Thank you Robert!!! I used the 2nd option as that is a better way to handle this issue. Thanks for your suggestion too.

SteCoxy

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!?

Screenshot 2019-10-17 18.16.09.png

Andrée Starå

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