Looking to take a parent row on the pulled column and put a percent complete of the children rows.

Chris Nicklaus · 08/18/20

Ben Goldblatt · 08/19/20

Hi Chris,

To calculate the % complete in the checkbox column (e.g. to use a formula to populate a numeric value in a column expecting boolean values), try using this type of formula in the parent row cell:

="" + ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"

percent of checked boxes result in the referenced checkbox column.png

Using the set of quotation marks at the beginning of the formula with the plus (+) sign before the rest of the formula will allow you to populate the formula result in the checkbox column you're referencing.

Thanks,

Ben

Ben Goldblatt · 08/18/20

Hi @Chris Nicklaus,

If I'm understanding this correctly, you're looking for a way to calculate a percentage of checked boxes on child rows in your sheet. If this is the case, as long as your "% Complete" column isn't being used for Dependencies you should be able to enter a formula on the parent row to calculate the percentage of checked child row boxes. Something like this should work:

=ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"

More information on the functions used in this example can be found in the following Help articles:

I hope this helps! If the above doesn't address what you're looking for, please provide some additional details on what you're hoping to accomplish and I'll be happy to assist further.

Thanks,

Ben

Chris Nicklaus · 08/19/20

I'm looking to take the checkbox row which I'm calling pulled. I would like to put a percentage complete of the children rows into the parent row. So the percent complete is in the same column as the checkbox's.

Ben Goldblatt · 08/19/20

Hi Chris,

To calculate the % complete in the checkbox column (e.g. to use a formula to populate a numeric value in a column expecting boolean values), try using this type of formula in the parent row cell:

="" + ROUND(IF(COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)) > 0, COUNTIF(CHILDREN(Checkbox1), 1) / COUNT(CHILDREN(Checkbox1)), 0) * 100, 0) + "%"

Using the set of quotation marks at the beginning of the formula with the plus (+) sign before the rest of the formula will allow you to populate the formula result in the checkbox column you're referencing.

Thanks,

Ben

Chris Nicklaus · 09/18/20

Now 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

Thanks,

tlong2b · 01/11/22

I'm finding the above formula to be unparseable. What am I doing wrong?

Genevieve P. · 01/12/22

Hi @tlong2b

What are your column names? Ben's formula above references a column called "Checkbox". If you have a different column name you'll need to update this portion of the formula to match your sheet.

Cheers,

Genevieve

K Mitchum · 02/27/24

Hi Genevieve!

I am trying to do something similar, and have adapted this formula, but it is not calculating correctly, could you help me with what I am doing wrong? I am using the below formula, and screenshot is attached of what I see.

Thanks for any help you can provide!

="" + ROUND(IF(COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)) > 0, COUNTIF(DESCENDANTS(Completed8:Completed12), 1) / COUNT(DESCENDANTS(Completed8:Completed12)), 0) * 100, 0) + "%"

Genevieve P. · 02/27/24

Hey @K Mitchum

Within the DESCENDANTS function you'll want to reference the parent cell, so most likely the cell in the current row. Since you're putting the formula in the same column as you want to evaluate, you actually don't need a reference at all.

="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Let me know if that fixes it for you 🙂

Cheers,

Genevieve

K Mitchum · 02/27/24

Hi Genevieve!

Amazing, I think that worked! I will add to all my other sheets now and confirm if I have any more trouble!

Really appreciate your help and quick response!

Genevieve P. · 02/27/24

Awesome! Glad I could help 🙂

K Mitchum · 03/19/24

Hi Genevieve!

Me again! I have a similar issue with the percent complete calculations on a different sheet, but this time I am wondering if there is something to add that will Exclude the parent rows in the overall total calculations (row "Project 1 - PV"). Screenshot below to show what I mean.. I am using the following formula for calculation currently, but it is not showing 100% complete because there are no checkboxes in the 3 parent rows but not sure how to exclude those specific rows here. Thank you!!

="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

Genevieve P. · 03/20/24

Hi @K Mitchum

No problem! We can subtract the count of direct Child rows from your count of Descendants in general. Try this:

="" + ROUND(IF(COUNTIF(DESCENDANTS(), 1) / (COUNT(DESCENDANTS()) - COUNT(CHILDREN())) > 0, COUNTIF(DESCENDANTS(), 1) / (COUNT(DESCENDANTS()) - COUNT(CHILDREN())), 0) * 100, 0) + "%"

Looking to take a parent row on the pulled column and put a percent complete of the children rows.

Best Answer

Answers

Help Article Resources

Categories