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
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?
Comments

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/504619columntypes
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/2476171createandeditformulasinsmartsheet