Formula for different children percentages

darthmetal

So I've only been using Smartsheet for about a week, I've used excel in the past but am not too knowledgeable about formulas. What I want is to create a formula where the children tasks have a different, hidden percentage to influence the parent task completion %. For example, if I had a task to install power circuits as a parent task and the 3 children tasks are: 1) Obtain quote for power install 2) Approve PO for quote and 3) Install power. Now, if I were to mark off the first 2, you would naturally get about 33.3% of the whole task complete if those two children tasks are 100 % done . But in reality, its much easier and is much faster to complete the first 2 tasks than the last . The first 2 tasks can take 1 week, and the last task can take several months. So is there a way to place a value of the first 2 tasks more at let's say 10% done and the last at 90% ? So that way we can reflect 100% complete for the first two tasks but the overall completion is more like 20%. I realize I can change it manually but of course a formula would be much better :)

Paul Newcome

Try entering durations. Using your example above, lets just say 5 days for tasks 1 and 2 and then 180 days for task 3.

When you check the first box we now have 5 of 190 days completed. When you check off the second box you have completed 10 of 190 days.

First we use a SUMIFS to determine how many days have been completed.

=SUMIFS(CHILDREN([Hidden # Of Days]@row), CHILDREN([Completed Checkbox]@row), @cell = 1)

Then we divide that by the total number of days:

=SUMIFS(CHILDREN([Hidden # Of Days]@row), CHILDREN([Completed Checkbox]@row), @cell = 1) / SUM(CHILDREN([Hidden # Of Days]@row))