Comparing Actual % vs Expected %, and returning a status

Mohammed Sulaiman

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.

Sean Morgan

Hello @Mohammed Sulaiman ,

For the parent row status, you may instead wish to add an alternative Formula to insert a status, based on the children status types, rather than relying on percentages.

Alternatively, you could create a standard Grid Sheet with two percentage Columns if you are not required to use other Project Sheet features.

Let me know if you have any questions

Regards

Sean

Ahmad Azaizeh

Hello @Mohammed Sulaiman 

Did you find a solution or any workaround for your problem? I'm facing the same situation.

Anyone can help please?

Regards

Ahmad