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.
Answers
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!