Project Health Formula to Auto Populate a Symbol

Steve O'Hara

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

Mark Cronk

Try:

=IF(OR([% Complete]@row = 1, Start@row > TODAY(7)), "Green", IF(AND(start@row<=today(), [% complete]@row<1)), "Red", "Yellow")))

Mark

Steve O'Hara

Hi Mark,

Thanks for getting back to me but that comes back as #UNPAR

Paul Newcome

Make sure you are using the correct column names that are in the sheet.

Steve O'Hara

Hi Paul, Yes the column names are correct still come back as #UNPARSEABLE

Paul Newcome

Can you copy/paste the exact formula directly from the sheet to here?

Paul Newcome

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.