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.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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?

  • Surfer_Jet
    Surfer_Jet ✭✭
    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.

    Please the attachment.

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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
    Surfer_Jet ✭✭
    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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Awesome, thanks for sharing your solution!

  • Surfer_Jet
    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.

  • Kariv
    Kariv ✭✭✭
    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})

  • Genevieve P.
    Genevieve P. Employee Admin
    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!

  • Kariv
    Kariv ✭✭✭
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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: