Calculate % Complete, but ALSO retain automated roll-up?

Ken Hankoff

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 roll-up 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!

Paul Newcome

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)

Ken Hankoff

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.

Paul Newcome

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.

David Joyeuse

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

Ken Hankoff

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.

Paul Newcome

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)

Ken Hankoff

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.

Paul Newcome

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.

Ken Hankoff

Pushed the wrong button in haste, and don't know how to un-push it. Still - gave you credit @Paul Newcome

Gracias!

Paul Newcome

Happy to help. 👍️