Parent Row % Complete Doesn't make any sense

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?

InkedParent Row Target % Complete Doesn't make any sense_LI.jpg

Find more posts tagged with

Sheets

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.