Formula for RYGB Symbols using % Complete AND Due Date
Hello all,
I'm trying to create a formula in the "Health" column that will display RYGB symbols to indicate the following:
Blue = 100% (Complete)
Green = greater than 50% (In Progress)
Yellow = less than 25% (At Risk)
Red = 0% (Delayed)
I am to use the "% Complete" column AND "Actual Due Date" column to determine what the progress is on a project for higher ups to track easily.
As far as the Actual Due Date, I'm not sure how to incorporate it, if you can give me some ideas or direction it would be highly appreciated.
Thank you!
Answers

So far this is the closest I've come and I'm getting an error message saying "circular reference"
=IF(AND([Actual Due Date]@row < TODAY(), NOT(ISBLANK([Actual Due Date]@row)), NOT(Status@row = "Complete"), NOT(ISBLANK(Status@row)), NOT(Health@row > 0)), "Red", IF(AND([Actual Start Date]@row < TODAY(), NOT(ISBLANK(Status@row)), NOT(Health@row > 0)), IF(OR([% Complete]@row = 0, ISBLANK([% Complete]@row)), "Yellow", IF(AND([% Complete]@row > 0, [% Complete]@row < 100, NOT(ISBLANK(Status@row)), NOT(Health@row > 0)), "Blue", IF(OR([% Complete]@row = 100, NOT(ISBLANK(Status@row)), Status@row = "Complete", NOT(Health@row > 0)), "Green")))))

This worked for the dates but how do I add the % Complete into it?
=IF([Actual Due Date]@row > TODAY(60), "Green", IF([Actual Due Date]@row >= TODAY(), "Yellow", "Red"))

Hi @afelo,
For your original set of criteria the formula would be:
=IF([% Complete]@row = 1, "Blue", IF([% Complete]@row > 0.5, "Green", IF([% Complete]@row < 0.25, "Yellow", IF([% Complete]@row = 0, "Red"))))
However, there is a potential problem with this  between 50% and 25% there is a blind spot as you can see using the sample data from above:
I would suggest changing the ranges to have this included.
With regards to the dates, you would need to decide what the criteria for the health based on date would be. You can then combine this with the % complete to make an IF(OR) or IF(AND) nested formula.
As an alternative, you could make a formula for the status column and/or the conditional formatting of status to display colour for this cell (or row).
Hope this helps; if you've more questions or more data then just post! ☺️

Thanks for your response.
I see what you mean. Is there a way to write the formula to say between 25% and 50%?
Or would it have to be two formulas that say greater than 25% and less than 50%?
How do I write a nested formula for AND in the IF formula, if say my due date preferences were:
IF the due date is in the past AND the % Complete is between 25% and 50% then Yellow
IF the due date is in the past AND the % Complete is less than 25% then Red
IF the due date is in the past AND the % Complete is greater than 50% then Green
IF the due date is in the future AND the % Complete is between 25% and 50% then Yellow
IF the due date is in the future AND the % Complete is less than 25% then Yellow
IF the due date is in the future AND the % Complete is greater than 50% then Green
IF the due date is in the past AND the % Complete is 100% then Blue
IF the due date is in the future AND the % Complete is 100% then Blue
These are just examples but if you can please convert them into formulas so that I can test them out and tweak them if need be, I'd seriously appreciate it.
Thank you.
Help Article Resources
Categories
Check out the Formula Handbook template!