% Complete is Not Working

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

Smartsheet Percent Column.JPG

Find more posts tagged with

Formulas

Project Management

Comments

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

Smartsheet Percent Column1.JPG

Smartsheet Percent Column_Formula.JPG

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

Sign in to join the conversation!

Quick Links

Trending discussions

Looking for assistance with a formula
Hi all, I am new to formulas and functions in Smartsheet and have a large sheet that I would like to summarize using an "average" formula/function. I need to include at least 2 separate criteria to filter correctly and I am having trouble combining the avg and collect functions as suggested in the functions list. I have…
Need help in formula function
I'm not able to get this formula to function in Smartsheet. The material ordered date is 9/3/2025. Am I missing something to get the date rage to populate? =TEXT([Material Ordered Date]@row +28,"MM/DD/YYYY")&"-"&TEXT([Material Ordered Date]@row +42, "MM/DD/YYYY")
I am trying to find a value in a table based on 2 criteria.
I am trying to find a value in a table based on 2 criteria. One of the values is dynamic from a drop down menu, while the other is static value in a column. The first value is found in column "Building type" and the other is found in Resource type. The table I am referencing is also shown. The result would be shown in the…