Need a Formula that only applies to Parent rows

Misty Clark

I am trying to figure out a formula that will only apply to parent rows. I want the Parent % complete to calculate on the total percentage of all child rows. The embedded formula depends on a duration, and I do not want that.

The current formula I have is this:

=IF((COUNT(CHILDREN(Primary@row)) > 0),AVG((CHILDREN([% Complete]@row))),([% Complete]@row))

I'm not sure what the second part of my if statement should be, since I want it to be whatever % has been entered for the Child row.

I was hoping to also make this a column formula to apply to all rows, but not sure if that is possible.

Paul Newcome

I have cleaned up some unnecessary sets of parenthesis to make it easier to read, but it looks like it should be working. If the count of children is greater than zero, average the children, otherwise output the % Complete.

=IF(COUNT(CHILDREN(Primary@row)) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

Misty Clark

Thanks for the response Paul. Unfortunately it is still coming back Unparseable.

Genevieve P.

Hi @Misty Clark

Is it possible that the column names are spelled wrong? Can you post a screen capture of your sheet, but block out sensitive data? It would be helpful to see the formula open so we can see if the cells are highlighted correctly in different colours.

Thanks!

Genevieve

Paul Newcome

I agree with @Genevieve P.. First check that the column names in the formula are spelled exactly as the column names in the sheet. If that still isn't it, a screenshot of the column headers as well as a screenshot of the formula in the sheet (similar to below) would be very helpful.

Misty Clark

=IF(COUNT(CHILDREN(Primary@row)) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

Hope this helps!

Paul Newcome

Ok. So you do not have a column called "Primary". Try this...

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

Misty Clark

Closer! But that gave me a circular reference warning.

Paul Newcome

This will need to go in a separate % Complete column, or you would need to apply an adjusted formula to only the parent rows.

=AVG(CHILDREN())

Misty Clark

Hmm, it says Blocked when I tried adding a separate % complete column. I was hoping to have one formula I could use dynamically as a column formula so I don't have to keep copy and pasting the formula every time a project gets added.

Paul Newcome

Try removing it from the original % Complete column. That should clear up the #BLOCKED issue in the second % Complete column.

Misty Clark

That worked! Thanks so much Paul!

Paul Newcome

Happy to help. 👍️