At-risk Formula Help

Options

I know this has been posed 7 million times already, but I need help with an at-risk formula...

I would like a flag in the "At Risk" column to automatically populate if the following criteria is met for that given row:

IF a row's "End Date" is within 15 days from today’s date AND "% Complete" for that row is less than 50%

OR a row's "End Date" is in the past

OR that row's "Status" column is yellow or red

**no row should flag as at risk if the above criteria is not met

Tags:

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @EggsnBlakeon

    =IF(OR([End Date]@row <= TODAY(), Status@row = "Red", Status@row = "Yellow", AND([End Date]@row <= TODAY(15), [% Complete]@row <= "49%")), 1, 0)

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    For that request, you don't have a parameter for: End date is in the past, but the status / completion is good. Is that accurate?

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • EggsnBlakeon
    Options

    @Colleen Patterson Thanks for your help!

    Great point and distinction. Instead of a row's "End Date" is in the past, the parameter should be a row's "End Date" is in the past but status is NOT blue*


    **blue means 100% complete

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    =IF(OR(AND([End Date]@row <= TODAY(), Status@row < > "Blue"), Status@row = "Red", Status@row = "Yellow", AND([End Date]@row <= TODAY(15), [% Complete]@row <= "49%")), 1, 0)


    @EggsnBlakeon this should work, as we added the parameter where where we will check for the end date is in the past and the Status @row does not equal Blue.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • EggsnBlakeon
    Options

    @Colleen Patterson that formula returned an INVALID OPERATION error

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @EggsnBlakeon

    =IF(OR(AND(Status@row <> "Blue", [End Date]@row < TODAY()), AND([End Date]@row <= TODAY(15), [% Complete]@row <= 49), Status@row = "Red", Status@row = "Yellow"), 1, 0)

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • EggsnBlakeon
    Options

    @Colleen Patterson (thanks for your continued help, btw) — That formula removed the error, but resulted in incorrect outputs (see below):

    • Row 1 shouldn't be flagged as it's just the project header and there's no values that meet the criteria to flag
    • Rows that have "blue" in the status column are flagged despite being 100% complete


  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @EggsnBlakeon

    Happy to help. Not sure what is happening, as the formula is working on my end (I did modify for the checking of blanks listed below). In my example, which I checked, I am flagging rows that are appropriate, and not the "blue" values that you are showing.

    Things to check: are your date columns configured as dates? That is needed when working with TODAY as a command.

    In order to account for Blanks, we are going to modify the formula to check for isblank at the beginning, based on the end date.

    =IF(ISBLANK([End Date]@row), 0, IF(OR(AND([End Date]@row < TODAY(), Status@row <> "Blue"), AND([End Date]@row <= TODAY(15), [% Complete]@row <= 49), Status@row = "Red", Status@row = "Yellow"), 1, 0))


    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @EggsnBlakeon just looked at your data again, and realized that you have the percent complete formatted as precents, where I do not, so we will need to adjust the formula to account for that discrepancy:

    =IF(ISBLANK([End Date]@row), 0, IF(OR(AND([End Date]@row < TODAY(), Status@row <> "Blue"), AND([End Date]@row <= TODAY(15), [% Complete]@row <= .49), Status@row = "Red", Status@row = "Yellow"), 1, 0))

    this is adding a decimal to the % complete section to return is a value is going to check for this to flag anything 49% or less.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!