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%.
Answers

I don't see any actual outputs in your health formula. What logic are you trying to create?

@Paul Newcome I'd like to add logic to the below that will state if :
Today vs. Finish Date '>1 and Actual Completion Percentage % is '<1 then the task is OD (Overdue)
and IF Today vs. Start Date '>1 and Actual Completion Percentage % =0, then the task has a LS (Late Start)
IF Today is '<Baseline Start, Schedule Variance '>= 0.8, the the task has a SV (Schedule Variance)
Now, in addition can the formula factor in when there are child rows that are ahead of schedule

@Paul Newcome would you be so kind as to help with this one, please?
Today vs. Finish Date '>1 and Actual Completion Percentage % is '<1 then the task is OD (Overdue)
and IF Today vs. Start Date '>1 and Actual Completion Percentage % =0, then the task has a LS (Late Start)
IF Today is '<Baseline Start, Schedule Variance '>= 0.8, the the task has a SV (Schedule Variance)
Now, in addition can the formula factor in when there are child rows that are ahead of schedule

I see that you posted this question again over here:
Did Darren's response give you the formula needed?
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!