Daily Percentage Completions based on check box
Happy New Year!.
I am trying to calculate daily % completions at Parent based on the weighted hours for each task.
If the task is complete then 100% of the weighted hours are counted, 50% if the task is in progress.
The current weighted formula is just the task/total hours at parent.
I will appreciate if someone can assist with the formula for the completion %.
Thanks
Rav
Answers
-
Hi @Smartsheet User ,
I think this will be cleaner if you add a text/number helper column [Wt Complete]. In that column place this formula in the child rows:
=IF([complete]@row=1,[weight]@row, IF([In Progress]@row=1,[weight]@row *0.5,0))
In the parent row of [Wt Complete] use:
=SUM(CHILDREN())
In [% Complete] use the formula:
=[Wt Complete]@row/[weight]@row
Your child rows should be 100%, 50% or 0%. Your parent row will be a weighted % based on the children.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Thanks for the quick help.
The formula's work great except the % Complete shows a zero or 1. The % display will be better to track completion rates. I 'll use this cell/column to push alerts on completion rates daily.
Could you please help with better formula for [Weight column] and perhaps display as percentage. I am not too confident in the one I am using below:
=[Duration in Hours]@row / [Duration in Hours]14 * 100
Thanks so much.
Rav
-
Hi Rav,
Your weighting methodology looks fine. If you're going to use the same methodology (total of 100 pts distributed by % of time required for a task) for each project you don't need the % Complete column because your Wt Complete is your % Complete.
You could format your columns so they have the same number of decimal places in all rows. You could divide the wt complete by 100 and display as a percentage: =IF([complete]@row=1,([weight]@row/100), IF([In Progress]@row=1,([weight]@row *0.5)/100, 0))
To format your column numbers highlight the column and then click the format icon you want to use. See screenshot below.
If formating the column as above isn't working for you, you can hard code the percentage. =ROUND([Wt Complete]@row/[weight]@row),2) +"%"
Happy to help more.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi Mark,
Thanks so much. This is working out great. Could you please help in adding only today's (start) date and status = scheduled to the formula's.
Thanks
Rav
-
Hi Rav,
I'm not clear on where you want to add the date and status criteria.
The basic formula statement to add into a COUNTIFS or SUMIFS function will be:
[start date]:[start date], = Today()
And
[status]:[status], ="Scheduled"
Help enough that you can make it work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives