Health formula for schedule variance

Melissa Torrez

I am trying to determine the health of a task based on schedule variance.

If the schedule variance is:

'>0.9 green

'<0.8 red

between 0.8 and 0.9 yellow

=IFERROR(AVG(CHILDREN()), IF([Actual Completion Percentage %]@row = 1, "", IF(AND(Variance@row > 1, [Actual Completion Percentage %]@row < 1), 0), IF(AND(Variance@row > 1, [Actual Completion Percentage %]@row = 0), IF(TODAY() < [Baseline Start]@row, " ", IF([Schedule Variance]@row = 1, "")))))

HeatherD.

Hi @Melissa Torrez !

Based on your description, it looks like this should do the trick:

=IF([Schedule Variance]@row>0.9,"Green",IF([Schedule Variance]@row<0.8,"Red","Yellow"))

This translates to: If the variance is greater than 0.9, show green. If the variance is lesser than 0.8, show red; otherwise (if it's between 0.8 and 0.9) show yellow.

Best,

Heather

Melissa Torrez

@HeatherD. I am trying to take into consideration the parent/ child rows, the actual completion percentage and baseline start date. If the actual completion percentage is 100% then it is automatically green regardless of the variance between the baseline start date and the start date. So, I was also trying to take into consideration the variance (in days) between the baseline dates and actual start/finish dates. Does that make sense?

HeatherD.

@Melissa Torrez We're getting there! If the below doesn't work, will you please make an outline of exactly what criteria match each color? That way we can map the formula accordingly.

But in the meantime, adding in the Actual Completion Percentage:

=IF(OR([Actual Completion Percentage]@row=1,[Schedule Variance]@row>0.9),"Green",IF([Schedule Variance]@row<0.8,"Red","Yellow"))

This translates to:

If either the Actual Completion Percentage in this row is 100% OR the Schedule Variance in this row is greater than 0.9, show green.

Otherwise, if the Schedule Variance in this row is less than 0.8, show red.

Otherwise (if the variance is between 0.8 and 0.9), show yellow.

If there are additional criteria you want to outline, we can add them in to the formula.

Best,

Heather

Melissa Torrez

@HeatherD. We are getting closer, thank you! I still think I need to take into consideration the date columns to show for example that a task is (green) ahead of schedule if the actual completion percentage is anything greater than 0% and the baseline start is in the future. I also want to show if the baseline start date is in the past and no activity has started (0%) that we are behind schedule so in the original formula I was looking to the variance column to see if there was a variance between the start/ finish dates and the baseline start/ baseline finish dates. I also don't want any color to show if the task is 0% and the dates are in the future

HeatherD.

@Melissa Torrez Let's try this:

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

Melissa Torrez

@HeatherD. That works for the most part, but I think I might be missing another element.

In the example below the 2 lines are displaying yellow which is right if you look at start compared with baseline start. The task was supposed to have started according to our baseline but the schedule variance is blank.

The formula for schedule variance is below. Maybe I have something wrong here?

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

HeatherD.

@Melissa Torrez It looks like the variance formula is performing correctly - it's just a matter of whether it's exactly what you want.

In looking at our Health formula, I think I may see the issue:

=IF(OR([Actual Completion Percentage]@row = 1, [Schedule Variance]@row > 0.9, AND([Actual Completion Percentage]@row > 0, [Baseline Start]@row > TODAY())), "Green"

This states that if any of these three statements are true, then show green:

• Actual Completion Percentage is 100%
• Schedule Variance is greater than 90%
• Actual Completion Percentage is greater than 0 AND Baseline Start is after today

Based on the section in bold, the two rows in your screenshot are only going to be green if the Actual is more than 0. Try adding an = sign in there if you want it to be greater than or equal to 0:

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

Melissa Torrez

@HeatherD. would you be able to look at/ help with the schedule variance formula as I think there is something still wrong.

If you look at the top parent row, the schedule variance is 0.3 and the health color is yellow but this should be red since it is < 0.8.

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())), "Green", IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Yellow", IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), "")))

HeatherD.

@Melissa Torrez Well! Somehow it looks like the red component is missing. Let's dissect:

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

IF(OR([Schedule Variance]@row < 0.8, AND([Baseline Start]@row < TODAY(), [Actual Completion Percentage %]@row = 0)), "Red",

IF(AND([Schedule Variance]@row >= 0.8, [Schedule Variance]@row <= 0.9), "Yellow",

IF(AND([Actual Completion Percentage %]@row = 0, [Baseline Start]@row > TODAY()), ""))))

The portions I edited/added are bolded. So, put together it would be:

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