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.
Always happy to work through questions or concerns!
-
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.
Always happy to work through questions or concerns!
-
@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.
Always happy to work through questions or concerns!
-
@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.
Always happy to work through questions or concerns!
-
@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.
Always happy to work through questions or concerns!
-
@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.
Always happy to work through questions or concerns!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 434 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!