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/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
