Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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:

  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.

Best Answers

  • ✭✭✭✭✭
    Answer ✓

    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

  • Answer ✓

    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?

  • ✭✭✭✭✭
    Answer ✓

    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

  • Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions