# Formula to automate the symbols on a sheet.

Options
✭✭

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:

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,

Tags:

• ✭✭✭✭✭
Options

What you seek is possible:

=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.

Andrew

He who fails to plan is planning to fail. - Winston Churchill

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭✭✭
Options

What you seek is possible:

=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.

Andrew

He who fails to plan is planning to fail. - Winston Churchill

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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.

• ✭✭
Options

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!

• ✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!