Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Project Health Based on task completion vs project completion

MTaylor ✭✭
edited 12/09/19 in Archived 2017 Posts

I am trying to determine a way to communicate/graphically represent project progress based on today's date in relation to the start date and projected end date of a project. So if we have a 90 day project and we are on day 50 I want to see Project Progress of 55%

I already have a data point for Task Completion percentage and with those two data points I can then hopefully find a formula to give me project health where the variance between project progress and task completion determines the project health status. So i'd want something like if Task Completion is 1% to 5% less than Project Progress Yellow Ball or Flag or other symbol, if Task Completion is 6% to 10% less than Project Progress, red symbol.  If Task Completion is more than 10% less than Project Progress, Critical. If Task Completion is greater than, equal to or less than 4% from Project Progress Green symbol...

Is this possible?


  • Shaine Greenwood


    It's definitely possible. RYG symbols are a great way to represent health. Details on the symbol (and all of our symbols) can be found in the help article here: https://help.smartsheet.com/articles/504619-column-types

    You can drive the automatic change of the RYG symbols with a formula, such as an IF statement. An example of how you can do this is below:

    =IF([Task Completion]1 < (0.06 + [Project Progress]1), "Red", IF([Task Completion]1 < (0.1 + [Project Progress]1), "Yellow"))

    A few things to note when you create your formula:

    • The above is just an example to represent how the formula can look, I'd recommend not copying it.
    • You'll want to put the formula in all of the cells of your Symbol column.
    • IF statements will run from left to right until the first condition is met, so I'd recommend building your formula from smallest increment to largest.
    • Symbols have a text value associated with them, which is how they are represented in a formula: values like "Red" and "Yellow."
    • Percentages are represented as values between 0 and 1 in Smartsheet, with 0 = 0% and 1 = 100%, meaning 0.06 = 6%

    I'd recommend checking out our article on building formulas in Smartsheet as well: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

This discussion has been closed.