% Complete is Not Working

kadam56146

Hello,

I have a spreadsheet with about 85 tasks and subtasks, the % Complete isn't working. It's show the percent completed as 99%, when only about 10 tasks have been completed for this project. I tried to disable the Dependency Settings and use the =AVG(CHILDREN()) formula but then I got the divide by 0 error. So then I tried =IFERROR(AVG(CHILDREN())) and still received an error.

What is the secret to get the % Complete to calculate all the tasks even if it has not started and the % Complete is 0 or blank?

Thank you,

Kim

 

Andrée Starå

Hi Kim,

You must have dates and duration for the % Complete for it to work. 

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

kadam56146

Thank you, that does help but have to say I'm a bit disappointed that these are requirements because for tracking issues during Sprints, I wouldn't know what the duration or dates will be until we move through each Sprint. So this feature in Smartsheet really does not help me.

Andrée Starå

Happy to help!

More info that might help: 

When Dependencies are enabled, Parent Rows are non-editable in the columns listed above. If you'd like to use your own calculations you can either disable the Dependency functionality on the sheet, or create your desired formula in one of the columns not being used in the dependency functionality. Custom formulas can’t be used to change the display or bars in the Gantt chart.

https://help.smartsheet.com/articles/765753-parent-rollup-functionality

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors#Complete

Best,

Andrée

Andrée Starå

I'd be happy to take a look!

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

kadam56146

I think I found my workaround. To explain my process a first, I am a PM and track tasks via Sprints, which are every two weeks. So I have a list of Jira tickets organized by Epics. We then schedule a hand full of tasks every two weeks for the current Sprint, therefore, I am not able to enter durations, or dates until tasks are assigned to a Sprint.

To track the overall progress of the project, I want to document anticipated start/end dates and then the actual start/end dates... again, I don't actually know these until Sprint grooming.

So, because I do not know durations, or dates, the Dependency settings will not work.

Here is my work around...

I used the =AVG formula to calculate the average for the entire column. However, I know this isn't the "exact" average because if I want to do it right, I would do the AVG on only my Epics, maybe I'll have time to do that later but for now this is close enough. Then in my Epics % Complete, I used the =AVG(CHILDREN()) and had to put 0% for each Jira ticket.

Here's some screen shots, one to show the values and one with the formulas.

If you know of a better work around, that would be great.

Thank you,

Kim

Andrée Starå

Great to hear that you found a workaround!

I'll get back to you if I think of something else.

Have a fantastic weekend!

Best,

Andrée