weighted task %, % complete by time

CDupe

Hi,

I'm having trouble wrapping my head around how to produce a formula for my scenario.

1. 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.
2. For simplicity sake, I would like to report % complete based off of time elapsed for each task.
3. 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?

Matthew Flebbe

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

CDupe

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?

NadBen

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.

John_Foster

Hi @NadBen @CDupe

If you were to wrap the previous formula with the one below, if would return 0 for ones that are in the future.

=IF([Start Date]@row>TODAY(),0,"existing_formula")

Hope this helps :)

John