or Explore Discussions

# Formula to automate the symbols on a sheet.

09/17/21
Accepted

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:

• What you seek is possible:

=IF([email protected] = "", "", IF([email protected] <= 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

• Try this...

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

thinkspi.com

• What you seek is possible:

=IF([email protected] = "", "", IF([email protected] <= 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

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

• Happy to help. 👍️

thinkspi.com