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.

Visualise Planned v Actual Effort

Stuart Reed1
edited 12/09/19 in Archived 2017 Posts

Hi. I was originally using RAG balls to denote if a development task would be delivered on or within the estimated time (display green ball) or if late (display red ball).

  • We use Initial estimate column to estimate the dev in days
  • We use an Actual Hours logged column (cumulative) for the developer to log their Actual Hours - which increases each day.
  • We used Estimated hours remaining column which is manually reduced (usually) every day
  • We then created a column called Total Hours to sum up Actual Hours + Estimated Hours Remaining
  • Finally, we have a Health Column to display Red, Yellow, Green Balls depending on if the Total Hours will be > or < than the Initial Estimate. The formula we use is

=IF(([Initial estimate]3 * 8) >= [Total Hours]3, "Green", "Red")


Problem - As you see, yellow doesn't get used at all, and whilst the formula is ok, the customer never knows when we might deliver under the estimate as its hidden under Green. So we would like to be more transparent by displaying


Green Health when Total hours < Initial Estimate

White Health for Total hours = Initial Estimate

Red Health for Total hours > Initial Estimate


I think I need a nested if statement but I keep getting errors. Can anyone help, or can anyone suggest and model a slicker approach to the above.



This discussion has been closed.