Making % Complete Column Accurately Reflect Work Done

Emily B

Hello,

 

I am looking for a way to make the % complete on my parent rows reflect only work that takes effort, rather than processes that are just elapsed time.

 

For background, my group uses smartsheet for project management purposes, but we also use it for billing. Our parent rows in our project sheets are all billing categories, for example, we charge $x for "Data Collection" and $y for "Data Analysis," but each is a long process with many steps, some of which take effort/resources/man-hours, and some of which are just elapsed time. At set times (ie quarterly) we check the % complete of the parent rows for our projects, and bill our clients for the percent of the total that is complete. So if I have an ongoing task that takes many days but no effort, it contributes disproportionately for the % complete, so it may say we are 20% complete when we have really done 70% of the work.

 

I know this may not seem like a serious problem but it would help our smartsheet meet multiple important needs for our group.

 

The elapsed time feature has been helpful but obviously does not fix this issue. It would be great if there is some way to designate a row so that it will not count for the total % complete calculation. 

 

Any ideas?

Brett Evans

Build another column that is called %effort

 

Assume for example Parent Task A with Child tasks X,Y and Z.  And asume that X is 50% of the effort, Y is 40% of the effort and Z is 10% of effort.

 

Then the formula for %effort for X would be .5 * % Complete.  Y and Z would follow suit.  You have to use =Sum() on the parent row, Task A.

 

Example linked.

 

https://app.smartsheet.com/b/publish?EQBCT=ed30c557e5ea4c92bf15a7ccf1ca973b

JohnHinkle

You could add an additional %effort column with this formula:

 

=AVG(CHILDREN()

 

This will show an average of child row percent complete. It wouldnt show a weighed average like the automatic percent complete parent row does, but it could be a good way to show the % of work done, only on items with a percent. 

Lauren

Hi John- I'm new to Smartsheet. I tried your %effort foumula but I get an error message #DIVIDE BY ZERO

My column headings are:

Start Date

Due Date

Date Completed

%effort

For two child tasks, Date Completed is blank.

How do you suggest I structure the sheet to take advantage of =AVG(CILDREN()

Thanks,

Lauren

JohnHinkle

Lauren, if you do not have any values in the child row %effort column then you will get that error (because its trying to average but there are no numbers). Once you add percent values to those cells, the error will go away. You can also add 0% as a temporary number until you have the correct %. 

Lauren

John-

Excellent! There are 3 child tasks. One is 100% complete and the other two 0% complete.

The parent row now shows .33333 instead of 33%. How do I convert decimal to percentage?

Thanks, Lauren

JohnHinkle

Click in the cell and press the Percent Format button on your toolbar (towards the bottom). You can also format the entire column by clicking on the column header (which will highlight the column) and press the Percente Format button