Project Health Formula to Auto Populate a Symbol
Hi
I have a formula that flags the health of each row (task) via a RAG status. Green is healthy, Amber is at risk or in progress and Red is a failure.
This is set up to work off the start date and % complete. However, it just isn't right.
The formula is;
=IF(OR([% Complete]@row = 1, Start@row > TODAY(7)), "Green", IF(OR(Start@row <= TODAY(), Start@row = TODAY(1)), "Red", IF(OR([% Complete]@row < 1, Start@row >= TODAY(7)), "Yellow")))
The issue I am having is that it is showing red if the task has started and % complete is greater than 0%. What I want it to display is the following
Green - 100% complete or not due to start for 7 days
Amber - If less than 7 days to start or if beyond irrelevant of % complete or Start date is in the past and % complete is greater than 0%.
Red - If The start date is today or in the past and the % is 0
From this I run a report over various projects so I can see what is at risk. At the moment with the current formula it is showing the project is at risk even though it has started and the % complete is greater than 0.
Thanks in advance.
Steve
Answers
-
Try:
=IF(OR([% Complete]@row = 1, Start@row > TODAY(7)), "Green", IF(AND(start@row<=today(), [% complete]@row<1)), "Red", "Yellow")))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Thanks for getting back to me but that comes back as #UNPAR
-
Make sure you are using the correct column names that are in the sheet.
-
Hi Paul, Yes the column names are correct still come back as #UNPARSEABLE
-
Can you copy/paste the exact formula directly from the sheet to here?
-
If you are using @Mark Cronk's formula, I believe he may have fallen victim to those rather obnoxious parenthesis as we all do at some point. Try removing one of the closing parenthesis from the end.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!