weighted task %, % complete by time
Hi,
I'm having trouble wrapping my head around how to produce a formula for my scenario.
 We are considering each task (hierarchy 1) a percentage of the overall project. For example, research is 5% of the project, production is 25%, etc.
 For simplicity sake, I would like to report % complete based off of time elapsed for each task.
 Then I need to report on total % complete in the parent row (hierarchy 0) taking into consideration the weight (percentage of total project) of task and it's % complete by time elapsed
Any thoughts on a formula to use for parent and child rows?
Answers

Hi @CDupe,
Working with weighted percentages is always a fun exercise. The following solution could be simplified by adding some helper columns. I have solved it without any helpers.
For the parent row, you can simply use =sum(CHILDREN())
For each of the child rows, you will need to adjust a couple of numbers according to the weight you give to each task.
=IF([End Date]@row <= TODAY(), 0.05, (TODAY()  [Start Date]@row) / IF([End Date]@row = "", NETDAYS([Start Date]@row, TODAY()), NETDAYS([Start Date]@row, [End Date]@row)) * 0.05)
The above formula is for a 5% weight. For 10% you would change 0.05 to 0.10. 15% would be 0.15, etc.
Breaking down the formula:
First Part:
IF([End Date]@row <= TODAY(), 0.05
If the End Date is less than or equal to today, then the task is complete and the full value of the weight is applied)
Second Part:
(TODAY()  [Start Date]@row) / IF([End Date]@row = "", NETDAYS([Start Date]@row, TODAY()), NETDAYS([Start Date]@row, [End Date]@row)) * 0.05)
Else If the End Date is in the future, then:
Calculate the net number of days that have elapsed from the start date and divide by the total number of days to complete the task (% Complete),
Multiply the % Complete by the Weight of the task.
Essentially you wind up with a percent of a percent.
Hope that helps. Let me know if you have any follow up questions.
Matt

Hi @Matthew Flebbe ,
Thank you so much for responding! This formula seems to work as long as the start date is in the past. If the start date is in the future, it's yielding a negative number. Any thoughts?

Hi @CDupe and @Matthew Flebbe
I would like to know how to navigate away from the negative result caused by the future Start Date too, please.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!