I am tryings to create a risk formula for Program that has list of projects as a child. Following are conditions to mark the project as either Green, Yellow, Red or Blue:
- Blue:
- Project has progress (% Complete > 0) but hasn't officially started yet (Start Date is in the future)
- Indicates early progress or a potential data entry issue - Green:
- Complete (100%)
- OR In Progress and on track (within 10% of Expected % Complete) - Yellow
- In Progress but behind schedule
- OR Not Started but start date is still in the future - Red
- Not Started when it should have started (Start Date passed)
- In Progress but critically behind schedule
- End Date passed but the task is incomplete
Above conditions are for projects. ALSO if any project(s), child, is Red, entire Program, parent, turns red.
To achieve this I wrote a Program row formula as follows:
=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red",IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow",IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "Green")))
and for child, Project Level formula as follows:
=IF(AND([% Complete]@row > 0, TODAY() < [Start]@row), "Blue",IF(OR(AND([% Complete]@row = 1, Status@row = "Complete"),AND(Status@row = "In Progress", [% Complete]@row >= [Expected % Complete]@row - 0.1)), "Green",IF(OR(AND(Status@row = "In Progress", [% Complete]@row < [Expected % Complete]@row - 0.1),AND(Status@row = "Not Started", TODAY() < [Start]@row)), "Yellow",IF(OR(AND(Status@row <> "Complete", TODAY() > [Finish]@row),AND(Status@row = "Not Started", TODAY() >= [Start]@row)), "Red","Red")))))
But it says its UNPARSEABLE. I have also attached a pdf export of this file. Any help would be greatly appreciated.
