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

Options
✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

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?

• edited 04/28/23
Options

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.

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

Options

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

• edited 05/15/23
Options

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!

Options

Awesome, thanks for sharing your solution!

• Options

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.

• ✭✭✭
Options

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})

Options

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!

• ✭✭✭
Options

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