Calculate % Complete, but ALSO retain automated rollup?
Hi Smartsheet Community,
Has anyone successfully built a way to automatically enter a % Complete for a Task (row), but also still allow for that attribute to rollup to a calculated % Complete at a parent level in a worksheet that has Dependencies enabled?
I would like for a user to be required to select from a list of choices to communicate progress on a Task. Choices would be "Not Started", "Just Started", "In Progress", "End in Sight", and "Complete". By selecting one of those five choices, a % Complete column would have one of the following corresponding five choices automatically populate: "0%", "25%", "50%", "75%", "100%".
The problem I encounter is that if I lock a formula into the % Complete column to achieve the automation I'm seeking, the % Complete column will no longer roll up at the parent levels, unless I build a formula to do that at each level of the hierarchy, and I don't want to bother with figuring out a formula for weighting the percentages, and having to enter it again each time the hierarchy changes.
Has anyone done something like this before, and if so, can you share the approach?
Thanks!
Best Answers

You could write two formulas. One for the Parent rows and one for the Child rows. Then you can combine them into an IF statement so that it will run the appropriate formula based on the hierarchy so that you do not have to worry about two separate formulas in a single column.
=IF(COUNT(CHILDREN()) > 0, parent_row_formula, child_row_formula)

I see the logic, Paul. However, I believe this means that whenever the hierarchy structure is modified, the formula will have to be entered into the corresponding row(s). That may happen automagically via Smartsheet, but I'm wondering whether it could break the formula if someone were to insert a new row, but not copy the formula into it.
I may need to refresh my memory as to how formulas carry into new rows. I thought it was just those added to the bottom of matrix, but maybe I've got that wrong.
Additionally  I'm not confident in my ability to develop a formula that calculates the % Complete in the same way that Smartsheet does it. Math is my arch enemy, and even the thought of weighting an average makes me a little panicky.
Cheers.

Formulas will autofill into new rows so long as there are two rows of the same hierarchy above and/or below the new row that also contain the formula. This means that autofill will work for new rows inserted at the top, in the middle, and at the bottom of a sheet.
As for the formula... Smartsheet has a weighted average function AVGW.
Answers

Hi @Ken Hankoff
I believe Projects in Smartsheet are designed so that when you assigned a task (row) to someone and asks for an actualization, that person have to enter a value in the % completed cell.
So putting formulas within the % complete is not gonna work really well.
Also choosing from a drop down list is not gonna sit well within a % column. You'd rather want to put values within it 0, 0.25, 0.5, 0.75 or 1 (those not being part of a list)
Hope it helped

Hi David,
Agreed that formulas within % Complete column, although they can work, disrupt the standard functionality of that attribute. I don't think my objective can be achieved, unfortunately, but was wondering whether I was missing something that someone with a different perspective might have accomplished.
Thanks for the comments.

You could write two formulas. One for the Parent rows and one for the Child rows. Then you can combine them into an IF statement so that it will run the appropriate formula based on the hierarchy so that you do not have to worry about two separate formulas in a single column.
=IF(COUNT(CHILDREN()) > 0, parent_row_formula, child_row_formula)

I see the logic, Paul. However, I believe this means that whenever the hierarchy structure is modified, the formula will have to be entered into the corresponding row(s). That may happen automagically via Smartsheet, but I'm wondering whether it could break the formula if someone were to insert a new row, but not copy the formula into it.
I may need to refresh my memory as to how formulas carry into new rows. I thought it was just those added to the bottom of matrix, but maybe I've got that wrong.
Additionally  I'm not confident in my ability to develop a formula that calculates the % Complete in the same way that Smartsheet does it. Math is my arch enemy, and even the thought of weighting an average makes me a little panicky.
Cheers.

Formulas will autofill into new rows so long as there are two rows of the same hierarchy above and/or below the new row that also contain the formula. This means that autofill will work for new rows inserted at the top, in the middle, and at the bottom of a sheet.
As for the formula... Smartsheet has a weighted average function AVGW.

Pushed the wrong button in haste, and don't know how to unpush it. Still  gave you credit @Paul Newcome
Gracias!

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!