Help with Sites and Sheets

Jake Friedberg

I'm struggling to create two things in my Smartsheet. I'd like to have a countdown in the completation date from "today" until our project's expected final day.  And capturing the %done of the project in smartsheet, maybe I'm doing something wrong but the =AVG() is not working for me. I'd like the program health to indicate with the RGY balls instead of the "Good" how many projects are within the dates ranges of being completed.

both of these additions are to be pulled into my Site, in the picture below. I know sites can do no sort of arthmatic and most of this is formula work, so if somebody could please help me with this in mind who knows sheets very well, it would be much appricated. 

Kara Lumley

Hi Jake,

Here's a few formula exampls for the RYG Program Health - 

=IF(AND([Due Date]2 + 7 < TODAY(), %Complete2 <> 1), "Red", IF(AND([Due Date]2 < TODAY(), %Complete2 <> 1), "Yellow", "Green"))

 

To be used in a "RYG" Symbol column, referencing a "Due Date" and "% Complete" Column (typically in a task row).  The ball will display as red if a task is over 7 days past Due and is not 100% complete. The ball will be yellow if the task is not 100% complete and is past due but not by more than 7 days . Otherwise, the ball will be green.

 

=IF(%Complete3 <= 0.33, "Green", IF(AND(%Complete3 > 0.33, %Complete3 < 0.66), "Yellow", IF(%Complete3 >= 0.66, "Red", "")))

 

To be used in a "RYG" Symbol column, referencing a % complete column. Depending on how complete a task is, the ball will change from red, to yellow, to green. (100% being broken down in to 33%, 66% and 99%)

 

With regards to Percent Done, we can calculate that for you automatically if your sheet has dependencies enabled - see this article.

 

 

I hope this helps!

Kara