# Nested At Risk Flag Formula

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)

• ✭✭✭✭✭

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!

• 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?

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

• ✭✭✭✭✭✭

Happy to help. 👍️