Expected % Completion, Schedule variance and health formulas


I have set up a series of calculations to determine expected % complete, schedule variance and the health (red, yellow, green) of a task given those variables but I am not sure it is capturing all scenarios that may need to be considered.

Expected % Complete formula: =IFERROR(AVG(CHILDREN()), IF(Finish@row <= TODAY(), 1, IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, TODAY()), 0) / NETWORKDAYS(Start@row, Finish@row)))

Schedule variance formula: =IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN()), IFERROR([Actual Completion Percentage %]@row / [Expected % Complete]@row, " "))

Health formula: =IF(OR([Actual Completion Percentage %]@row = 1, [Schedule Variance]@row > 0.9, AND([Actual Completion Percentage %]@row >= 0, [Baseline Start]@row > TODAY())), IF(AND([Schedule Variance]@row >= 0.8, [Schedule Variance]@row <= 0.9), IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), ""))))

In the below sample, there are some instances where a task is ahead of schedule but the expected % complete is not taking that into consideration because there is no variance in the baseline start and baseline finish dates when compared to start and finish. See Discovery & Design where they are actually 80% complete, but expected % complete is only at 54%.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!