Formula for RYGB Symbols using % Complete AND Due Date

afelo

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!

afelo

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")))))

afelo

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"))

Nick Korna

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! ☺️

afelo

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.