"At Risk" Formula

Jessica Takata
Jessica Takata ✭✭✭
edited 12/09/19 in Smartsheet Basics

Hi,

Searching for help to create a formula for the "At Risk" column that turns the flag red when the following criteria is met:

- Anticipated Due Date is in the past

- % Complete is not 100%

- Status is not "On Hold" or "Canceled"

I found this article that helped with the first two criterion, but I still can't seem to get the last one right. We *could* just delete the date for those statuses, but there are times when we still want to capture that information even if the project/task is on hold or canceled.

Help?

Thank you!  

At Risk Formula Help.png

«1

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try this one and let me know if it works for you. 

    =IF(AND([Anticipated Due Date]@row <= Today(), [% Complete]@row&lt;>1,OR(Status@row <>"On hold", Status@row <>"Canceled")),1, 0)

  • Thank you for the quick reply! I'm getting a "BOOLEAN EXPECTED" message?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Jessica,

    Right-click on the column header and tell me what type of symbol column you are using? The formula I gave you was for the flag column. 

  • Hi,

    Yes, it's the flag symbol/column I'm using, but maybe I'm doing something wrong? I attached a screenshot of what I see when I right-click...

    Thank you.

    At Risk Formula Help_2.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you share a screenshot of the formula that you input that is giving you that response? 

  • Okay, so strange, but I just reinserted the formula and I'm no longer getting an error message (yeah!), but it also appears to still be turning red when On Hold and Canceled are chosen. The only thing I changed was  "Anticipated Due Date" to "Anticipated End Date" to match the column title.

    I attached a screenshot.

    Thank you!

    At Risk Formula Help_3.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Let's split that out from the rest of the formula because in ANY case, a cancelled or on hold project should be blank... Try this one: it looks for on hold or canceled and then puts a zero, if not, then it checks for the other items. 

    =IF(OR(Status@row <>"On hold", Status@row <>"Canceled"), 0, IF(AND([Anticipated End Date]@row <= Today(), [% Complete]@row&lt;>1), 1, 0))

  • It seems to turn them all "off" (white) now... But I see your point. I can request that the team maintain the details for On Hold and Canceled projects elsewhere and remove the dates from the smartsheet. This should fix the issue.

    Thank you for your help!

    At Risk Formula Help_4.png

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Whoops, I had an error there. 

    =IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([Anticipated End Date]@row <= Today(), [% Complete]@row&lt;>1), 1, 0))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    That edit should fix your issue. You should be fine having them track status in the same sheet. Let me know if that solved the problem! 

  • Yes! This appears to be working now.

    Thank you very much!

  • dhall
    dhall ✭✭✭✭

    Stumbled upon this thread and I know it's a couple years old but I'm curious if this formula still works. When I paste it into my sheet, it doesn't acknowledge the columns. Only thing I can identify is that the "&lt;" at the end of [% Complete]@row" is causing it.

    If I remove the "&lt;", the column names in the formula properly highlight, but then the formula doesn't work. Are these 4 characters in this formula important?

    I feel like this one makes more sense for what I'm trying to accomplish:

    =IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([End Date]@row <= Today(), [% Complete]@row<1), 1, 0))

    Only edits I made were to remove "&lt;", changed the % Complete to be LESS THAN 100% (1), and End Date instead of Anticipated End Date.

  • dhall
    dhall ✭✭✭✭

    Okay so I did a search for the "&lt;" and "&It;" (lowercase L vs uppercase I, respectively) in the Smartsheet community and it looks like every post that contains this in a formula is from 2018-2019, so it suggests it is no longer needed as part of the formula.

    Can we get someone to confirm that's the case and if so, is just removing it the solution to using this type of formula?

  • Hi @dhall

    In 2019 we migrated our Community from one platform to another. I believe the values you're seeing ("&lt";) are a remnant of how the data was copied. They're not a part of Smartsheet formulas and will give you an error.

    In regards to the formula, the structure should work! And yes, you would need to update the column names to be your own column names.

    =IF(OR(Status@row ="On hold", Status@row ="Canceled"), 0, IF(AND([End Date]@row <= Today(), [% Complete]@row <1), 1, 0))

    When you say this "doesn't work", are you receiving an error or an incorrect result? Can you confirm that your Status column also has the same dropdown values ("On hold", "Canceled")?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • dhall
    dhall ✭✭✭✭

    Hi @Genevieve P. thanks for the quick reply!

    That settles it, I just wanted to make sure there wasn't some part of a formula that needed &lt; and I was going crazy thinking how I could not have seen this before.

    I'm using this formula as a base to test potentially using the colored balls (red, yellow, green, blue) to for dates and % complete, but good to know I'm not crazy!