"At Risk" Formula

Options
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 ✭✭✭✭✭✭
    Options

    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)

  • Jessica Takata
    Options

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    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. 

  • Jessica Takata
    Options

    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 ✭✭✭✭✭✭
    Options

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

  • Jessica Takata
    Options

    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 ✭✭✭✭✭✭
    Options

    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))

  • Jessica Takata
    Options

    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 ✭✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    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! 

  • Jessica Takata
    Options

    Yes! This appears to be working now.

    Thank you very much!

  • dhall
    dhall ✭✭✭✭
    Options

    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 ✭✭✭✭
    Options

    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?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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")?

  • dhall
    dhall ✭✭✭✭
    Options

    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!