Hello! What is the formula to calculate percent complete for a task?
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
-
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?
-
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. 🤔
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
Awesome, thanks for sharing your solution!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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.
-
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})
-
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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P. So there is no way I can derive the weighted average of a select group of child tasks?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives