Nested At Risk Flag Formula

Caitlin Clark-Howell

New to formulas! Would love a review:

Flag if any of these bullets are met:

1) "Status" = Planning, In Progress, In review AND #times due date pushed is greater than 0

2) "Status" = Planning, In Progress, In review AND due date is in the past

3) "Status" = Roadblock OR Paused

4) "Health" = "Down"

Here's my failed formula:

=IF(AND([# Times End Date Pushed]@row > 0, AND(Status@row = "Planning", Status@row = "In Progress", Status@row = "In Review")), 1), IF(Health@row="Down",1), IF(AND(Status@row = "Planning", Status@row = "In Progress", Status@row = "In Review"), [End Date]<Today(),1), IF(Status@row = "Roadblock", Status@row="Paused"),1)

Paul Newcome

I personally would go with something like this:

=IF(OR(Health@row = "Down", Status@row = "Roadblock", Status@row = "Paused", AND(OR(Status@row = "Planning", Status@row = "In Progress", Status@row = "In Review"), OR([End Date]@row < TODAY(), [# Times End Date Pushed]@row > 0))), 1)

David Joyeuse

Hi @Caitlin Clark-Howell

I'm not sure I'm reading you well.

Do you want all these to flag the same column or not?

If yes,

than it would be more along the lines of for each of your cases:

• 1: AND(OR([Status]@row="Planning", [Status]@row="In Progress", [Status]@row="In Review"), [# Times End Date Pushed]@row > 0)
• 2: AND(OR([Status]@row = "Planning", [Status]@row = "In Progress", [Status]@row = "In Review"), [End Date]<Today(),1)
• 3: OR( [Status]@row = "Roadblock", [Status]@row = "Paused")
• 4: [Health]@row = "Down"

Now, since 1 & 2 have the same [Status] parameters: "Planning, In Progress & In Review", we can mix one and two with:

• AND(OR([Status]@row="Planning", [Status]@row="In Progress", [Status]@row="In Review"), OR([# Times End Date Pushed]@row > 0, [End Date]<Today(),1))

Add in 3 & 4 to lake an OR altogether and we do have:

=IF(OR(AND(OR([Status]@row="Planning", [Status]@row="In Progress", [Status]@row="In Review"), OR([# Times End Date Pushed]@row > 0, [End Date]<Today(),1)), [Status]@row = "Roadblock", [Status]@row = "Paused", [Health]@row = "Down"),1 ,0)

Note that you don't need to add the OR for case 3, as it's already taken into account in the first one.

Hope it helped!

Caitlin Clark-Howell

Thanks for the quick reply @David Joyeuse! Agree with your logic in combining statuses in criteria 1 & 2. I plugged in the formula you drafted and it still came back unparseable. Any ideas?

Paul Newcome

I personally would go with something like this:

=IF(OR(Health@row = "Down", Status@row = "Roadblock", Status@row = "Paused", AND(OR(Status@row = "Planning", Status@row = "In Progress", Status@row = "In Review"), OR([End Date]@row < TODAY(), [# Times End Date Pushed]@row > 0))), 1)

Caitlin Clark-Howell

That worked beautifully @Paul Newcome -- thanks for saving the day :)

Paul Newcome

Happy to help. 👍️