% 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:
- 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 - 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!
Answers
-
Are you able to provide a screenshot for reference that shows your second solution that currently requires two columns?
-
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.
-
Exactly what formulas do you have in each of the two columns?
-
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
-
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?
-
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?
-
If you have dependencies enabled, you will not be able to replace the built in % Complete parent row calculations.
-
@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?
-
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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!