Formula to automate the symbols on a sheet.

09/17/21
Accepted

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,


Best Answer

  • Andrew StillsAndrew Stills ✭✭✭✭
    Accepted Answer

    What you seek is possible:

    Task color formula:

    =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

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try this...

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

    thinkspi.com

  • Andrew StillsAndrew Stills ✭✭✭✭
    Accepted Answer

    What you seek is possible:

    Task color formula:

    =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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

Sign In or Register to comment.