% Complete formula

Bjg

Hi,

I need to be able to calculate the % complete of a project in the parent row based on % complete of a number of tasks in the child rows and the project duration without using Predecessors column.

 

Can anyone help??

 

Thanks,

Bill

Dartwohl

Can you use the following formula in your parent row(s)?    

=AVG(CHILDREN())

 

Shaine Greenwood

Hi Bill,

The predecessors column isn't a factor in the parent row's % complete calculation. That calculation is based on the % complete in the child rows, weighted by the duration of each task.

If you're looking to calculate a simple % complete that isn't weighted by duration, then you can place this formula in the parent:

=SUM(CHILDREN()) / COUNT(CHILDREN())

Let me know if you're looking for something else and I'll be happy to advise further.

ruizdeaz36321

Hi Shaine, I need to have a formula in the % complete cell so it changes based on the status (RYG column). It looks like we still cannot use formulas here, how can I solve this?thanks in advance! 

Shaine Greenwood

Hello,

To do this, you'll need to create a new column for % complete, one that isn't being used for dependencies. 

Otherwise, you'll need to disable the % complete function of dependencies altogether. To do this, right-click any column header and select Edit Project Settings. In the dropdown under "% complete column" select None.

Although doing the above will make the grey progress bars no longer appear inside the Gantt bars, you might consider changing the gantt display label such that it displays your % complete column. Information on changing the display labels for the Gantt chart  can be found in this help article: https://help.smartsheet.com/articles/765755-modifying-project-settings

Giulliana

Hi

I am using Smartsheet for a few months, I am testing it in a two projects. So this might be a beginner user type of question.

I need to know how to make my '% Complete' column calculate the task group percentage by using 'Effort' instead of 'Duration' to weight the average?

I use Earned Value metrics, and our cost is based in labor hours, not duration. Using duration would give me a wrong 'Planned Value' result and would break all my metrics. t is only accurate at the task level, but it is wrong when you go up any level.

I am doing it manually, disabling the % complete in project properties, and adding the AVGW formula to each single higher level cell, but whenever the manager adds a new task row to the plan, it breaks the calculation and I have to fix it.

There must be a better way of doing this, otherwise it would not be feasible to implement the tool in large scale in a company that uses Earned Value Management. I using EVA in only one of the two projects, and it is already being a nightmare. I can imagine having to support several projects with the same work plan template.

Thanks

Brandyn Carlson

WHY is it impossible to use a calculated formula column for the % Complete Gantt chart functionality? This seems very basic wherein a % complete is calculated across multiple subtask columns and would be more broadly usable by Smartsheets user base than limiting columns to those requiring manual entry. I thought Smartsheet was trying to improve automation within their sheets... can someone confirm that this functionality is still disabled and why?

Mo McCullough

I second Brandyn's comment. Would LOVE to be able to use a formula in the % Complete column based on today's date (aka data that is constantly changing). Right now if I want the Gannt chart to display the % Complete within the task bar, I MUST COPY the formula information in one column and paste those values in the % Complete column for the Gannt chart completion task bar to be accurate. The fewer the manual steps in a sheet, the better. Is there a direct solution for this (rather than a time-consuming work-around)? Thanks!