% Complete based on efforts without lag in duration

Ted Hassanein

Hi everyone, wondering if someone has solution for this.

I am looking for a formula that will be within a single cell (not to create additional rows) that will MULTIPLY only if the DURATION is a child (=IF(COUNT(CHILDREN())) with the % COMPLETE of that specific duration.

At the moment I have 2 solutions:

1. Single cell with formula that will (SUM all DURATION if child & 80% complete) Divided by (SUM all DURATION if child) [=SUMIFS(Duration8:Duration37, Navigation8:Navigation37, 0, [% Complete]8:[% Complete]37, >0.79) / SUMIF(Navigation8:Navigation37, 0, Duration8:Duration37)] This solution is great but lacks accuracy
2. Add 2 columns that will have the DURATION if child and DURATION multiplied by Percent COMPLETE and the final cell with have SUM of 1 column divided by the other [=SUM([Effort X % Complete]8:[Effort X % Complete]37) / SUM([Total Effort]8:[Total Effort]37)] This solution is accurate however I do not want to add 2 new columns.

Does anyone know a way to have 1 cell solution with accuracy without adding 2 columns with some formula that will incorporate a "MULTIPLYIF" each row?

Thanks in advance!

Paul Newcome

Are you able to provide a screenshot for reference that shows your second solution that currently requires two columns?

Ted Hassanein

Hi @Paul Newcome , see attached screenshot showing 2 columns that create child duration (in days of 8 hours) and child duration multiplied by % complete.

Thanks so much for your interest.

Paul Newcome

Exactly what formulas do you have in each of the two columns?

Ted Hassanein

Hi @Paul Newcome

Column 1 (Total Effort): =IF(COUNT(CHILDREN()) > 0, 0, Duration@row)

Column 2 (Effort X % Complete): =IF(COUNT(CHILDREN()) > 0, 0, [% Complete]@row * Duration@row)

Hope this helps

Paul Newcome

Try this...

=IF(COUNT(CHILDREN()) = 0, AVG(CHILDREN()), [% Complete]@row * Duration@row)

Although wouldn't it work the same way if you just used

=[% Complete]@row * Duration@row

on every row since the % Complete and duration on the parent rows are already a rollup of the child rows?

Ted Hassanein

Thanks @Paul Newcome ,

The idea is to remove any lag on the project and only take into account actual durations (which for our case is effort). Therefore your question on roll up does not work as it takes lag into consideration in final value.

Thank you for that formula you provided, it is actually a cleaner way for me if I was able to add the columns and hide them.

Unfortunately my management is requesting we do not add any columns (given it is customer centric) and only replace the current default % Complete cell.

Do you know a way to have a SUM each "@row" multiplication if children?

Paul Newcome

If you have dependencies enabled, you will not be able to replace the built in % Complete parent row calculations.

Ted Hassanein

@Paul Newcome agree with you, however we have a Smartsheet Customer Success Manager that confirmed we are able to change it going forward.

Our company works on short to medium term projects so we are looking for accuracy on new projects going forward, not to revert backwards.

Hoping this answers your doubts?

Paul Newcome

I'd be very interested to see how they intend to change it with dependencies enabled. I have tested it in the past and even tried it again before my last post to make sure nothing had changed. I am on an enterprise account as well.

Ted Hassanein

Ok @Paul Newcome they had mention to us that they can allow the cell not to be a formula for any new going forward projects, will let you know if it happens!