Identifying children for a specific parent automatically

Hello Community Team,

I'm working on automating a % Effort column for my project plan. I need to avg the % effort at the parent level of its associated children. I want to do this automatically without having to identify the children under the parent.

I can use =avg(children()) but I will need to specify the children cells within the formula. I want the formula to automatically identify the children cells under that parent and calculate the avg.

Is there a way to do this?

Any help would be greatly appreciated.

Thanks in advance.

Find more posts tagged with

Formulas

Project Management

Comments

Shaine Greenwood

Hello,

Using the CHILDREN() function without a cell reference will have it automatically reference all of that cell's direct children. All you need to do is make sure that your sub tasks are indented underneath the correct parent.

More info on the CHILDREN function can be found in the help center: https://help.smartsheet.com/function/children

Screen Shot 2017-09-14 at 9.30.50 AM.png

Screen Shot 2017-09-14 at 9.30.58 AM.png

thadr

Thank you for the information Shaine. Yes, I am aware of this. I'm using that formula at the highest level.

Maybe I should have been clearer in my request. My request is more around when you have multiple parent/children relationships in your plan which is almost always the case. You have children who are parents to other child tasks and so on. Is there a formula to only capture the information of the children directly associated with the parent (level 1) without having to specify the cells. I only want to capture the children directly under the parent and not the additional layer of children underneath.

Again, I don't want to have to manually specify the child cells. I want the formula to automatically identify which are the direct children and only capture those 1st level children.

Thanks

Robin Sherwood

The "Children" function only applies to direct children not grand children. You would need to add it to each level to get an aggregate rollup. If you have added to each level then it is going to roll up.

The Children function can take a cell ref as a parameter, so you don't even need to have it in the same row/column. e.g. =CHILDREN([%Effort]6) If you do this instead of adding it to the actual column you can count the levels without placing the value in the column and affecting your aggregation.

Screen Shot 2017-09-15 at 11.07.46 AM.png

thadr

Thank you for the clarification, Robin. I see that Shaine was correct. I'm trying to setup a template where the user will not have to add a formula every time they create parent/child relationships. I want the sheet to do it automatically.

The closest I get is to ask the user to copy down the formulas to the rows they create. It can be as simple as dragging the formula down the columns rather than copying and pasting to individual cells. The user will enter the % Effort in one column and my formula will roll it up in another column. Where a child exists, it just copies the value that was entered by the user. I use the =AVG(Children()) formula at the parent level.

http://publish.smartsheet.com/cab882d657af4f59ae5d49a83129b9d1

Screenshot from 2017-09-15 14-04-14.png