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

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?