Help with #DIVIDE BY ZERO error message

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

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?

 

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!