% Complete based on efforts without lag in duration


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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!