Comparing Actual % vs Expected %, and returning a status


I'm trying to automatically calculate the expected % of both a task, and its sub tasks. Then compare it with the actual % complete entered manually by a user, and return status.

The formula I have used for calculating expected % is :

=IF(Level@row = 1, AVERAGEIF(CHILDREN(), >0), ROUND(IF(Start@row > TODAY(), 0, IF(Finish@row < TODAY(), 1, NETWORKDAYS(Start@row, TODAY()) / Duration@row)) * 100, 0) / 100)

Note : There is a zero level task, which reports, on overall completion. It is not shown in the screen shot, you can only see level 1 & 2.

The expected for the level 1 is calculated in this way, so that it doesn't considers task the weight of completion of tasks not started, i.e the start date is in future.

Now the status formula is as follows:

=IF([Actual % Complete]@row = 1, "Complete", IF(AND([Actual % Complete]@row >= [Expected % Complete]@row, ([Actual % Complete]@row > "0%")), "On Track", IF(AND([Actual % Complete]@row < [Expected % Complete]@row, ([Actual % Complete]@row > "0%")), "Behind", IF(AND(Start@row < TODAY(), ([Actual % Complete]@row = 0)), "Not Started/Behind", "Not Started"))))

Since the actual % is a system automated calculation, it takes into account of weight of completion of tasks in future.

Hence, in the above example the level 1 task is showing as "Behind", even though its child tasks are either complete or not started.

How can I mitigate this ? Shall I build a tolerance value in the status or is there a better way to do this ?

Many thanks for your help.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!