My risk formula for program row with child Projects progression is not working.
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.
Best Answers
-
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"))))Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
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.
Answers
-
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 ")".
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
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?
-
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"))))Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
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.
-
@Ronak happy to help! 😊
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!