Project Health Based on task completion vs project completion

MTaylor

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

Hello,

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