Hi Community,
I’m trying to build a reliable Planned % column that rolls up consistently, so I can compare it against % Complete at the project and phase levels.
Setup
- I created an Old Planned % column that calculates planned progress for each row based on Planned Start and Planned Finish dates:
=IF(OR([Planned Start]@row = "", [Planned Finish]@row = "", PARENT(Primary@row ) = "External Dependencies"), "",ROUNDDOWN(IF(TODAY() < [Planned Start]@row , 0,IF(TODAY() > [Planned Finish]@row , 1,NETWORKDAYS([Planned Start]@row , TODAY()) / MAX(1, NETWORKDAYS([Planned Start]@row , [Planned Finish]@row )))),2))
- For my Planned % rollup, I only use the children’s Old Planned % values and weight them by Duration:
=IF(OR(CONTAINS("External Dependencies", Primary@row ),COUNT(COLLECT(ANCESTORS(Primary@row ), ANCESTORS(Primary@row ), CONTAINS("External Dependencies", @cell ))) > 0), "",IF(COUNT(CHILDREN()) = 0,[Old Planned %]@row ,IFERROR(ROUNDDOWN(AVGW(COLLECT(CHILDREN([Old Planned %]@row ), CHILDREN([Old Planned %]@row ), ISNUMBER(@cell ), CHILDREN(Duration@row ), IFERROR(@cell , 0) > 0),COLLECT(CHILDREN(Duration@row ), CHILDREN([Old Planned %]@row ), ISNUMBER(@cell ), CHILDREN(Duration@row ), IFERROR(@cell , 0) > 0)),2), "")))
The issue
- With this setup, my Planned % at the top level comes out around 11%, while % Complete is only about 6%.
- The underlying rows are the same or even higher in % Complete, so I don’t expect Planned % to be higher at the overall level.
- If I instead use a direct formula at the parent (not just children), it jumps up to ~23%, which is way too inflated.
- So the weighted rollup is closer, but still not matching % Complete behavior.
What I’m looking for
Has anyone built a Planned % rollup formula that truly mirrors how % Complete is calculated in Smartsheet (but using planned values instead of actuals)?
I want both metrics to be on the same basis — ideally both duration-weighted — so the difference between them reflects real schedule variance instead of a math mismatch.
Any formula examples or best practice tips would be greatly appreciated!
@Paul Newcome Please advise
