Planned vs actual progress (hope this can help you)

Fabian Mentur

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")))

Stefan 7k

Nice idea,

if.... progress can be calculated in a linear way ;-)

Best regards

Stefan

Alvaro

 

Excellent contribution.

I appreciate your time and share this valuable information.

Best Regards

Alvaro.

Ade

Hi Fabian,

I used your formula and updated the column name but the result says "#UNPARSEABLE.

Note: instead of start1 and finish1, I used Star Date and End date since these are the names of my columns.

Can you help me? See screenshot

 

Adelina

 

ShobhitD

Hi Fabian,

Thanks for this contribution!

It really helped me in automating some tasks.

Shobhit

Md Ariful Islam

Hi Fabian Mentur

Thanks for the info. How you calculate Planed% in Summary Row.