# Need a Formula that only applies to Parent rows

Options

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.

• ✭✭✭✭✭✭
Options

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)

• Options

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

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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.

• Options

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

Hope this helps!

• ✭✭✭✭✭✭
Options

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)

• Options

Closer! But that gave me a circular reference warning.

• ✭✭✭✭✭✭
Options

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())

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

That worked! Thanks so much Paul!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!