Percent complete of a checkbox row

Chris Nicklaus

I want to take the Child rows and put a percent complete in the row I'm calling Grandparent I want it to be based off the child rows because the amount of Child rows in each parent group differ. So I want the percent complete to be waited off the over all check boxes of the Child rows

Genevieve P.

Hi @Chris Nicklaus

Since Parent 1 and Parent 2 are are children of your Grandparent row, you could use the CHILDREN function to get an Average of the percents below, ex:

=AVG(CHILDREN())

However, you mention a weighted percent. It sounds like you want to count up all the checkboxes under the Grandparent, separate from a different Grandparent (so not the entire sheet).

If so, I've come up with one way to potentially do this. Someone else may have a more concentrated idea, but for me the easiest way to achieve your goals would be to create two helper columns.

1 . COUNT column

The first helper column would simply return the number of children, using this COUNT formula:

=COUNT(CHILDREN([Primary Column]@row))

This would be through the entire column except when you have Grandparent Rows. The Grandparents would have a SUM of the children, so SUMMING the number that each Parent returns.

=SUM(CHILDREN())

2 . COUNTIF Column for checks

The second helper column would be similar, but instead of just counting children, it would count how many children in the checkbox column were checked, or 1, using COUNTIF.

=COUNTIF(CHILDREN(Checkbox@row), 1)

Then again, in the Grandparent row, you would have =SUM(CHILDREN()) adding together those checks.

3 . Final Formula in Checkbox Column

This would then allow you to take these values and create a percentage in your grandparent row:

=(Checked@row / Total@row) * 100 + "%"

Example sheet:

You can then hide those extra columns so they're not visible in the sheet itself.

Let me know if this works for you!

Cheers,

Genevieve