Percentage Complete Column (Breaks)

Greetings,

I've discovered that the % Complete Column in a few of my sheets has broken, is incomplete at calculating the true completion percentage for a project.

I suspect this might have happened somewhere along the line of adding/removing rows.

Is there any way to see/edit the formula to ensure it is counting all rows and not stopping midway through a sheet?

I have dedented and indented, tried removing and adding back rows, even removed dependencies then added back and cannot get the sheet to properly calculate the entire column instead of just some of its rows. (?)

PS: I did post this earlier today but never saw the post come up so this is a second attempt - hope I didn't duplicate.

Regards,

Find more posts tagged with

Sheets

Formulas

Project Management

Comments

Andrée Starå

Hi Jc,

It looks like the % Complete isn't selected/enabled in the project settings. Can you double check?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jcarter67946

Hello Andrée,

Is this what you're referring to as enabled?

jcarter67946

Thanks for the response!

Is this where you're referring to 'enabling' the column?

It is calculating, just not seeing the entire column.

Andrée Starå

It looks correct. Doesn't it change the % Complete in the parent if you change it in the children?

Best,

Andrée

jcarter67946

Nope, has me stumped!

It stops at a specific row. It's a bit like a SUM column in Excel when the formula isn't grabbing all of the rows you want calculated.

I even tried adding a new row near the top. It didn't affect the % Complete column until I dedented it all the way to the left, then reindented it back to where it belonged. Once I saw the new row included in the % Complete calculation, I moved it down to the bottom of the sheet, past the "break" I found - and viola - it no longer calculates.

There seems to have been a hard break in the formula for that column - like it does not know where the end of the sheet really is. (?)

Andrée Starå

Strange!

If you haven’t already, I would recommend that you reach out to the Smartsheet Support Team. Smartsheet Support Team

Best,

Andrée

jcarter67946

** UPDATE **

I've been poking at this long enough to notice some movement.

By adding new child rows within the parent rows that were not calculating, I've begun to get Row 1 calculation to see the missing Parents and change as I edit any of the child rows formerly missing.

I don't believe the missing Parents are weighted properly with the rest of the working Parents - but they are now moving the total in the Row 1 as I make changes to their individual % completions.

Also, my 'dummy rows' can't be removed either without returning me to my original situation. As long as I leave in the new dummy rows, I can use the original/real rows' % Complete to effect a change to the entire project % Complete again.

For now, I've labeled my dummy rows with 'Checksum' so I don't forget they're not relating to real tasks.

Very bizarre situation. I wished it had been as simple as manually editing/forcing the % Complete column formula to include all rows. (?)

Andrée Starå

It should include all rows automatically.

Best,

Andrée

jcarter67946

** "Break" Resolved **

I finally poked at this enough to realize what was happening.

When you set up dependencies, % Complete "weights" all the sub-tasks based on the 'Duration' column.

This changes the math dramatically! You cannot expect 10 tasks under a parent to all equal 10% each and, therefore, effect a relative change to your Parent % Complete.

If you have Task Start and Task End dates that are exponentially larger on some tasks than others, it will appear as if shorter-duration task completions are not being counted in the % Complete column.

In my example, I had single task duration of over 200 days in length while most all other tasks were only a day in length. Therefore, completion of the 200-day task looked like 99% of the project because of its duration. Visually, you expect a single task completion to not be most of the project - but it was based on its duration compared with all the others combined.

The other single-day tasks had to all be completed in order to add up to enough to get the top row % Complete to move.

Lesson learned on using Duration to "weight" % Complete. (whew)

Andrée Starå

Ok.

Great to hear that you solved it!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

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?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…