PMO Template

SarahScot

I am struggling with the PMO template and the Schedule Health column in the Project Plan, which is indicating 'Red' even after a Task is complete, with this formula.

=IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green"))

Could I use a formula to ask it only to count if "Status" is In progress, or Not started? Will this mess up other formulas in the sheet or the parent rows?

VBAGuru

Main dependents are what is in the Health column.

VBAGuru

Your current formula stops with the first condition being true. Any value over .1 is going to produce a "Red" result.

Not 100% sure if this is what you are looking for. This would be 0% is Red, 100% is Green and anything between being Yellow.

=IF([Schedule Delta (%)]@row = 0, "Red", IF([Schedule Delta (%)]@row = 1, "Green", "Yellow"))

SarahScot

Thanks for response. In the project template the "Schedule delta %" is based on number of 'actual' days/'predicted' days, so if the task is late but 'Completed' the "Health" column still shows as 'Red' - not sure if I am just misunderstanding the concept?!

VBAGuru

You could do it in the original way you suggested using the Status column. But this is reliant on just that column. You could also combine both together using 'OR'.

=IF(OR([Schedule Delta (%)]@row = 0,[Health]<>"Completed"), "Red", IF(OR([Schedule Delta (%)]@row = 1,[Health]="Completed", "Green", "Yellow"))

There are a lot of variants on this depending on your needs.

VBAGuru

Main dependents are what is in the Health column.

SarahScot

Great, thank you, I played about with your idea of OR but couldn't quite get it to work but the following works instead

=IF(Status@row = "Complete", "Green", IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")))