My risk formula for program row with child Projects progression is not working.

Ronak

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:

1. 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
2. Green:
   - Complete (100%)
   - OR In Progress and on track (within 10% of Expected % Complete)
3. Yellow
   - In Progress but behind schedule
   - OR Not Started but start date is still in the future
4. 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.

testing formulas.pdf

Melissa Yamada

Hello @Ronak

Your formula worked fine on my end.

Can you share which formula you add on P004?

This is the formula I used:
=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"))))

Ronak

Thank you @Melissa Yamada,

It works now. Apparently, I dragged the cell formula down instead of applying formula to entire column and later modify the Program cell formula.

Thanks again for testing my formula for me and helping me resolve the error.

Melissa Yamada

Hello @Ronak

Looked like you got an extra ")" at the end. The reason for the #Blocked is the #Unparseable and the cause of the #Unparseable is the extra ")".

Ronak

Hello @Melissa Yamada,

Thank you for your help. It worked …for some some extend. It supposed to return Blue for P004 as the condition calls for a progression made earlier than a start date. Am I missing something here? Can you help?

Melissa Yamada

Hello @Ronak

Your formula worked fine on my end.

Can you share which formula you add on P004?

This is the formula I used:
=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"))))

Ronak

Thank you @Melissa Yamada,

It works now. Apparently, I dragged the cell formula down instead of applying formula to entire column and later modify the Program cell formula.

Thanks again for testing my formula for me and helping me resolve the error.

Melissa Yamada

@Ronak happy to help! 😊