Expected % Completion, Schedule variance and health formulas

Melissa Torrez

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

Paul Newcome

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

Melissa Torrez

@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

Melissa Torrez

@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

Genevieve P.

Hi @Melissa Torrez

I see that you posted this question again over here:

https://community.smartsheet.com/discussion/96516/overdue-late-start-indicators-based-on-multiple-criteria

Did Darren's response give you the formula needed?

Cheers,

Genevieve