Visualise Planned v Actual Effort

Stuart Reed1

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.

 

Mike Andreas

You may want to review my postings on the thread attached here.  It is close to what you are looking for.  It will require attention to detail in setting up columns and data types properly.

 

https://community.smartsheet.com/discussion/does-anyone-have-formula-syntax-control-ryg-and-gray-symbols-sheet