Formula to automate the symbols on a sheet.

SiddV47

The Task Progress column is linked to the % Complete column. 

when someone inputs a value of 100% in the % complete column, the task progress changes to a Green dot, and the same goes for when the % complete is 0% - Red dot, 1-99% - Yellow dot.

I would like to add a gray dot in the mixture so I need a new formula to reflect the following:

Task progress dots definition

Red Dot = When the task is past the original finish date, The red dot should auto-populate on the task progress column and the Status column should have 'At Risk' text (not sure if the latter can be done?). The % complete column will remain empty.

Please let me know if this can be done? 

Current Column formula for Task Progress Column -

=IF(ISBLANK([% Complete]@row), " ", IF([% Complete]@row = 1, "Green", IF([% Complete]@row = 0, "Red", IF([% Complete]@row < 1, "Yellow"))))

Current Column formula for Status Column -

=IF(NOT(ISBLANK([% Complete]@row)), IF([% Complete]@row >= 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))

Thank you,

Andrew Stills

What you seek is possible:

Task color formula:

=IF(Finish@row = "", "", IF(Finish@row <= TODAY(), "Red", IF([% Complete]@row = 1, "Green", IF([% Complete]@row > 0, "Yellow", "Gray"))))

Status formula:

=IF([Task Progress]@row = "", "", IF([Task Progress]@row = "Red", "At Risk", IF(NOT(ISBLANK([% Complete]@row)), IF([% Complete]@row >= 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

If the finish date is blank and/or the % complete is 0% or blank the task and status will remain blank.

It's worth noting this is a redundant method of identifying the same information. However, I do know mangers often have their own way of looking at information and as such a case may exist for displaying the same information differently.

Paul Newcome

Try this...

=IF([% Complete]@row <> "", IF([% Complete]@row = 0, "Gray", IF([% Complete]@row = 1, "Green", "Yellow")))

Andrew Stills

What you seek is possible:

Task color formula:

=IF(Finish@row = "", "", IF(Finish@row <= TODAY(), "Red", IF([% Complete]@row = 1, "Green", IF([% Complete]@row > 0, "Yellow", "Gray"))))

Status formula:

=IF([Task Progress]@row = "", "", IF([Task Progress]@row = "Red", "At Risk", IF(NOT(ISBLANK([% Complete]@row)), IF([% Complete]@row >= 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

If the finish date is blank and/or the % complete is 0% or blank the task and status will remain blank.

It's worth noting this is a redundant method of identifying the same information. However, I do know mangers often have their own way of looking at information and as such a case may exist for displaying the same information differently.

SiddV47

Thank you for all your help @Paul Newcome and @Andrew Stills

Paul Newcome

Happy to help. 👍️

Nikki. A

To follow up on this, I have already used the below formula for my symbol:

=IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")))

The problem is the condition to enter this IF is that the Status is not Completed, thus the tasks that are Completed, end up not getting a color.

I was able to also try that this works: =IF(Status@row = "Completed", "green"))

But when I combine the two together, with the following formula I get an error

=IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green"))) OR(IF(Status@row = "Completed", "Green"))

Paul Newcome

@Nikki. A Try this:

=IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green")

Think of it this way...

=IF(Status@row <> "Completed", do_this, "Green")

The "do_this" portion is going to be those second and third IFs.

Nikki. A

Perfect!

Thank you so much!!!

I was thinking since the IF condition was on not completed, then I needed to have an OR that if it is completed, it should also be green.. but somehow my formula didn't work.. but what you explained makes sense.

You Rock!

Nikki. A

One more question:

The above formula works beautifully at a task level, is there a way for me to have a column formula that has above with the addition that for the parent task, instead of simply looking at due date, etc it actually looks at the children and if children are red or yellow the parent would then not be green

Nikki. A

To elaborate on my ask, what I am trying to achieve is for the parent to have a yellow or red if the number of children that are yellow or red are above a certain #.

So say my structure is :

Parent

Child 1

Child 2

Parent 2

Child 1

Child 2

My current formula, which you helped with works beautifully across all rows. but for parent rows, instead of looking at the simple formula of =IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green")

I want it to look at something like this : If parent IF(Status@row <> "Completed", COUNTIF(Health:Health, "Red") > 2, "Red", COUNTIF(Health:Health, "Yellow") > 5 , "Yellow", "Green")), "Green")

In other words, I want to use a column formula that uses =IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green") to calculate the children colors but for the parent colors uses something like this IF(Status@row <> "Completed", COUNTIF(Health:Health, "Red") > 2, "Red", COUNTIF(Health:Health, "Yellow") > 5 , "Yellow", "Green")), "Green")

Paul Newcome

First we need to establish the parent and child row formulas separately. Then we can combine them in a single IF statement that basically says...

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

Child rows it looks like you already have:

=IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green")

Parent rows are going to look more like this:

=IF(COUNTIF(CHILDREN(), "Red") > 2, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 5 , "Yellow", "Green"))

Then we combine the two:

=IF(COUNT(CHILDREN()) = 0, IF(Status@row <> "Completed", IF(TODAY() - End@row > 0, "Red", IF(TODAY() - End@row > -3, "Yellow", "Green")), "Green"), IF(COUNTIF(CHILDREN(), "Red") > 2, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 5 , "Yellow", "Green")))

D Barnes

Hi- I've tried the solution sent for Nikki, but it is not working for me. Could someone help?

I'm trying to change the health column based on the Actual Cycle Time column and am receiving Incorrect Argument Set. Here is my formula:

=IF([Actual Cycle Time]@row, "", "", IF([Actual Cycle Time]@row <= TODAY(), "Red", IF([Actual Cycle Time]@row = 1, "Green", IF([Actual Cycle Time]@row > 0, "Yellow", "Gray"))))

Below is my reference guide that I am trying to automate the health symbols. (Gray would need to be those cells that are blank.

Thanks!