Working With Children as Strings
Here's a tricky one that has me stumped.
I have parent/child arrangement of project task completion. Calculating total project completion by using a simple AVG(CHILDREN()) does not get me a true completion value for the entire project.
Someone is going to tell me I went to a lot of trouble when I didn't need to, but what I did to get true project completion status is to add the child-task values and to that, add the child count * 0.00001
what this does is provide a number that contains both the completion value (left side) and child count (right side). This allows me to - at the TopParent row to SUM(CHILDREN()) which gives me a total completion value and total children count of the project. Then using LEFT and RIGHT and VALUE, I can divide the completion value by the count of children and come up with a true project completion percentage (see screenshot).
My problem comes in if I get cute and turn the Parent-Child row fields into strings. I can't figure out how to pull out the number portion using the CHILDREN() function.
To keep the formula simple, I'll just SUM up the VALUEs of the LEFT 4 characters of each child string.
=SUM(VALUE(LEFT(CHILDREN(), 4))) gets me "Invalid data type"
I can individually call out the parend-child by row and it works.
=SUM(VALUE(LEFT([% Total Project]2, 4)), VALUE(LEFT([% Total Project]6, 4)), VALUE(LEFT([% Total Project]9, 4))) gets me a correct value of 5.95
Ultimately I got what I fully wanted in the (row1, %TotalProject) field (see attached) with....
=SUM(VALUE(LEFT([% Total Project]2, 7)), VALUE(LEFT([% Total Project]6, 7)), VALUE(LEFT([% Total Project]9, 7))) + " (" + ROUND(100 * (SUM(VALUE(LEFT([% Total Project]2, 4)), VALUE(LEFT([% Total Project]6, 4)), VALUE(LEFT([% Total Project]9, 4))) / SUM(VALUE(MID([% Total Project]2, 5, 3)), VALUE(MID([% Total Project]6, 5, 3)), VALUE(MID([% Total Project]9, 5, 3)))), 0) + "%)"
But again, I had to call out each parent-child field seperately. And for any project past this simple example grid would be a non-starter.
So how can I perform an integrated function to each child in CHILDREN() and have it roll up to the parent for processing?
Comments
-
=AVERAGEIF(DESCENDANTS([Primary Column]@row), NOT(CONTAINS("Parent", @cell)), DESCENDANTS())
Give this a try in the parent row and see if it helps you. It should take the average of all of the children without much fancy word parsing, and instead just using a commonality of all of the parent rows. They all have the word parent in them. You could also go another direction and reference the fact that only the parent rows have values in % AVG Child.
-
Sorry (also thanks) - the example attachment was built only for the problem. Any project I use this solution on would have the % Total Project column (normally called % Complete) only, and Parents and Parent/Child....and Children would hold a galaxy of names and titles. Can't key on any word commonality there
The % AVG Child is just a comparison column - showing that just taking the average of the children is not a true representation of total % complete. The % Total Project column looks and sums up all the child task completions and divids by the total volumne of child tasks.
Averaging the averages (in the example) gives me a 72% completion. Averaging all the child tasks together gives me a 66% completion.
Given that, I haven't looked into using DECENDANTS so I'll beat my head against that wall and see if anything shakes loose.
-
=AVERAGEIF(DESCENDANTS([% Avg Child]@row), isblank(@cell),Descendants())
Just use my other suggestion then (above). Or find another unique pattern that only pertains to your parent rows. Or make your own by adding a helper column with =if(iferror(count(children()),0)>0,1) and use said helper column as your criteria, then hide the helper.
-
Helper columns are the easy way. I want to do this in the same column.
-
So the your formula above doesn't work because the %AVG Child column doesn't exist in practice. and the one column % complete (or in example % Total Project) will not have blank fields. Parents will have a rollup average of the children beneath.
Using Descendants would also require the ability to know if the row was a parent or child. Can Smartsheed go through a range and tell me what's a parent and what's a child?
I think I'm back to my original problem of how to process each parent-child under the top parent using the CHILDREN() function.
I want to do a SUM(CHILDREN()) and in each of those children which are strings - perform the function of extracting number texts and turning them back into numbers and then summing those up in the parent.
Parent
Child#1 = 12ae(xxx
Child#2 = 07owpeihd
Child#3 = 34&*E#l;kl
at the parent level doing a SUM(CHILDREN()) and getting 53
what do I do to "CHILDREN()" to get it to extract the first two text characters and turn them into numbers to then be summed up at the parent. 12+7+34 = 53
I've tried SUM(VALUE(LEFT(CHILDREN(),2))) but doesn't work. I can break it out to SUM-VALUE-LEFT child#1, child#2, and child#3 and that does work, but I can't do that in a project of any size. I need to use the automation of the CHILDREN() command. Maybe including an @cell will allow me to individually process a child within the automation of CHILDREN(), but I haven't been able to figure it out.
-
You will need helper columns for this as CHILDREN(@cell) is not a valid reference and will return #UNPARSEABLE.
-
Yup.
Appears that is my only way.
I'd like to make CHILDREN() do the calculation, but I've run out of play time and need to move on.
Many thanks to L@123 and Paul
