Hello! What is the formula to calculate percent complete for a task?

Karen Hansard

Hello! What is the formula to calculate percent complete for a task?  I don't want to use the project settings % complete functionality, because I would like to calculate at the detailed task level.

Paul Newcome

It depends on how exactly you want it calculated. Do you want it to be calculated based on the current date/start date/duration? Is there a "Status" that could drive it?

Surfer_Jet

Smartsheet_Project-01.png

Howdy! OK, let's see if I can get some help! HELP ME! Yes, Mr. Newcome, I have a status but let's see if you are still around.

Basically, as the subject line says for this thread, "I would like to calculate at the detailed task level" to status.

Please the attachment.

I come from an Excel background. Smartsheet is turning out to be fabulous. 🤔

Genevieve P.

Hi @Surfer_Jet

Have you tried using the Change Cell Workflow? See: Change the Value of a Cell in an Automated Workflow

You could set your trigger to be when the Status changes, it updates the % amount you want in the other column. This would allow you to manually adjust a % Complete if it's different than the standard one as well.

Note that this % column wouldn't be able to be used within Project Settings if you have dependencies set up. This is because that column has a Parent Rollup functionality that means the data can't be changed on parent rows.

Cheers,

Genevieve

Surfer_Jet

Howdy Ms. Genevieve! I figured it out. Thrashed my head to end up with two simple formulas. DOH! LOL.

=IF(Status@row = "X Not Started", "Empty", IF(Status@row = "N/A Not Applicable", "Empty", IF(Status@row = "N/S Not Sure", "Empty", IF(Status@row = "CAN Cancelled", "Empty", IF(Status@row = "Y Start", "Quarter", IF(Status@row = "WIP Work in Progress", "Half", IF(Status@row = "OT On-Time", "Three Quarter", IF(Status@row = "RISK At Risk", "Three Quarter", IF(Status@row = "CWn Completed Work Needed", "Full", IF(Status@row = "Z Completed", "Full", IF(Status@row = "DELAYED", "Delayed", "Not Started")))))))))))

and then the average in the blue row. :)

=AVG([% Complete]9:[% Complete]28)

Thank you for the response. Much appreciated!

Genevieve P.

Awesome, thanks for sharing your solution!

Surfer_Jet

Oh my!

The formula in the previous comment goes in the "Health Status" column.

I did not include the formula below the blue row (%percentage complete column).

=IF(Status@row = "Y Start", 0.1, IF(Status@row = "WiP Work in Progress", 0.5, IF(Status@row = "OT On-Time", 0.7, IF(Status@row = "Z Completed", 1, IF(Status@row = "X Not Started", 0, "")))))

There! For people looking, you have to look at the attachment for this to make sense.

Kariv

Hi. I have a similar question. I have a "Business Unit" column in a project plan. I want to know % complete by Business Unit. I set up a metrics sheet with the Business Unit in the Primary Column. I've tried AVERAGEIF and SUMIF columns, but when I average all of the Business Units in the metrics sheet, it does not equal the % complete in the project plan parent row 1. Here is the formula I have in my metrics sheet: =AVERAGEIF({Sample Project Business Unit}, $[Primary Column]@row, {Sample Project % Complete})

Genevieve P.

Hey @Kariv

The reason you're seeing a different number is because when you use AVERAGEIF it will give you an overall average, looking directly at each number. However Project sheets using Dependencies will calculate a % Complete on Parent rows based on a weighted average, using the Duration of the Child Rows to determine the percent.

Here's an example I used in a different post to help clarify!

Kariv

Thanks @Genevieve P. So there is no way I can derive the weighted average of a select group of child tasks?

Genevieve P.

Hey @Kariv

Depending on what it is you want to do, you could potentially use the AVGW Function to add a weight.

With AVG or AVGW, you could use the Collect function to filter down your ranges. Here's an example!

Here are other Community posts that have more information:

• Manual % complete formula in the parent row
• Formula for parent row that calculates % of child rows, based upon date durations