Help with #DIVIDE BY ZERO error message

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

Screenshot 2019-10-17 18.16.09_1.png

Find more posts tagged with

Non-profit

Formulas

Support

IT & Operations

Project Management

Media & Entertainment

Comments

Paul Newcome

That's because there are no children rows which returns a zero. You can't divide by zero, so the error gets thrown. Try wrapping your formula in an IFERROR

=IFERROR(current formula, "whatever you want displayed if there is an error")

SteCoxy

Thank you for replying with you response. I am not the best with formulae sorry, so I've tried to do this but couldn't figure it out.

Through accident, I have managed to fix the cell by outdenting the according row, which has fixed the error message. It now displays as follows: 0/3 (0%). The logic is that it ought to calculate the progress of the children rows below.

Unfortunately, this has now caused a further issue in that it does not appear to be calculating the progress. I've done a screenshot to clarify what I mean here.

Any ideas on how to correct this?

Screenshot 2019-10-21 13.21.08.png

Paul Newcome

Setting the hierarchy back to where you wanted it, what would you want displayed in that row?

SteCoxy

It does appear to be displaying correctly: 0/3 (0%), so I'm happy for it to remain like that.

The issue I'm now having is that it does not appear to be factoring in the children rows below. My understanding is that it should calculate the progress of the children tasks below when they have been completed but after I've ticked them complete and saved, it still displays as "0/3 (0%)".

Is the formula wrong? Any ideas what it might need changing to?

Paul Newcome

Are you leaving the hierarchy as it is in your most recent screenshot?

SteCoxy

Do you think I ought not to?

By changing the hierarchy it appears to have got rid of the error message.

Paul Newcome

The hierarchy is entirely up to you.

One way is giving you an error, but the other way is not producing the desired results. Either way there is an issue.

I just need to know which one you want to stick with because the solutions are going to be different.

SteCoxy

I think the preferable option is to have the hierarchy as my understanding is that its aim is to show progress of the children rows underneath 0/3 (0%) - as each one is completed, I'd like for the parent row to show this.

Very much appreciate your help here!

Paul Newcome

Ok. Then we will use something very similar to what you are currently using. The difference is what we will use as our criteria for if one of the child rows is complete. Obviously we cannot say whether or not a box is checked, so we will simply search each of the children cells for the text of "100%".

=COUNTIF(CHILDREN(), CONTAINS("100%", @cell)) + "/" + COUNT(CHILDREN()) + " (" + ROUND(COUNTIF(CHILDREN(), CONTAINS("100%", @cell)) / COUNT(CHILDREN()) * 100) + "%)"

Give this a try and see how it works for you.

SteCoxy

Thank you so much!! This has worked - what a relief!!

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…