At-risk Formula Help

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

Screen Shot 2023-03-10 at 1.10.58 PM.png

Find more posts tagged with

Formulas

Accepted answers

All comments

Colleen Patterson

@EggsnBlakeon

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

Colleen Patterson

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?

EggsnBlakeon

@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

=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.

EggsnBlakeon

@Colleen Patterson that formula returned an INVALID OPERATION error

Colleen Patterson

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

EggsnBlakeon

@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

Screen Shot 2023-03-14 at 1.55.11 PM.png

Colleen Patterson

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

Colleen Patterson

@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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.