#Divide by Zero on AVG(CHILDREN) formula

Sarah McMorrow

Hoping someone can help here!

I have rows that are parent, child and grandchild.

The formula in the child row to give a percentage of grandchild that are complete is as follows: =ROUND(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) * 100) + "%"

I'd like the parent row to show the average of all the children but when I do =AVG(CHILDREN() I get a Divide by Zero error even though there are children and all have a percentage.

Can anyone help me figure this out? Included screenshot.

Paul Newcome

Your current formula is actually producing a text string instead of a numerical value.

You would need to add in another column. I would suggest taking this portion...

=ROUND(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) * 100)

And putting it in this new column. Also in this new column would be your AVG formula.

Then in the display column, you would simply have something along the lines of...

=[Other Column]@row + "%"

Sarah McMorrow

Is there any way to do what I am trying to accomplish in one column? I'm trying to avoid having too many columns for little purpose.

Paul Newcome

Because your original formula is producing a text string and you are wanting to run calculations that require numeric values, they have to be split. I have a few ideas that will need some testing, but I don't have much faith that they will work out.

Paul Newcome

I have done some additional testing and none of my other ideas worked out. The only way I was bale to accomplish what you are trying to do was to use an additional column. The additional column can be hidden after everything is set up if clutter is your concern.

Sarah McMorrow

Thank you for investigating!

Paul Newcome

Happy to help! 👍️

Sorry I wasn't able to get it all in one column for you.