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
Answers
-
=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.
-
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.
-
@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
-
=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.
-
@Colleen Patterson that formula returned an INVALID OPERATION error
-
=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.
-
@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
-
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.
-
@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
Categories
Check out the Formula Handbook template!