Planned vs actual progress (hope this can help you)

Options
Fabian Mentur
Fabian Mentur ✭✭
edited 12/09/19 in Smartsheet Basics

Hello everyone, 

I'm a little bit new on smartsheet and the first thing that I faced was how to measure the variance of my project, I tried the forum, youtube and help from this page to get what I wanted, it was not what i expected. So to get that visibility I decided to ad a new column (% Planned) that shows the calculation based on the duration of each row and a RGYB (red,green,yellow,blue) column to have a visual indicator.

 

The formula that I used for the %planned was:

=ROUND(IF(TODAY() < Start1, 0, IF(TODAY() >= Finish1, 1, NETWORKDAYS(Start1, TODAY()) / NETWORKDAYS(Start1, Finish1))), 2)

and for the indicator was:

=IF(OR([% Complete]1 >= 1, IF(ISBLANK([% Complete]1), 0, [% Complete]1) > [%Planned]1), "Green", IF(AND([%Planned]1 = 0, (IF(ISBLANK([% Complete]1), 0, [% Complete]1) = 0)), "Blue", IF(IF(ISBLANK([% Complete]1), 0, [% Complete]1) >= ([%Planned]1 - 0.15), "Yellow", "Red")))

smiley

Smartsheet.JPG

Comments