Parent Row % Complete Doesn't make any sense

LLaCosta

See attached image

The Parent Row shows 7% complete based on the 1 item being 100% and all other items being in the future. The target % complete shows 20% which makes no sense to me. 

There is a formula for Target % complete:

=IFERROR(IF(Status@row <> "Completed", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) > 1, "Future", IF(NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row) < 0, "Late", IF([Planned Start]@row = [Today (hidden)]$1, 1 / NETDAYS([Today (hidden)]$1, [Planned End]@row), 1 - (NETDAYS([Today (hidden)]$1, [Planned End]@row) / NETDAYS([Planned Start]@row, [Planned End]@row)))))), "Missing Dates")

 

What am I doing wrong?

Genevieve P.

It looks like you are using Project Settings, which means that the % Complete on the Parent row will calculate a weighted percentage based on both the Duration and % Complete entered on each child row.

In this way, completing child rows with shorter durations won’t affect the Parent row % Complete as much as completing a longer duration child row. You can read more about this in our Help Center article on Parent Rollup Functionality, here. 

In comparison, it looks like your formula is based on the overall start & end dates without being weighted by the duration of the child tasks, which is why it is returning a different percentage. 

Lindsay AR

@Genevieve P Are there any formulas you are aware that to have a target complete that accounts for weighted percentage rather than just linear? This has been troubling me for weeks and I see multiple forums about it. I have spoken to account reps at SS and the suggestion is buy an add on (thousands of dollars) that has this or pay thousands or dollars for a formula to be developed. Planned complete is basic project management functionality.

I'm just really hoping someone out there smarter than me has a formula for this. Can the same formula (no idea what it is) that rolls up % Complete be used for a Target Complete?

Genevieve P.

Hi @Lindsay AR

Yes, there are ways you can build a similar formula yourself, using the AVGW function (see here)!

You may want to check out this other Community post that has an example built out: https://community.smartsheet.com/discussion/comment/248580#latest

If that doesn't help with your specific project, I would suggest creating a new Question in the Community with a screen capture of your sheet (blocking out sensitive data) and a detailed description of how you want the percent weighted so we can help with the syntax.

Cheers,

Genevieve