Daily Percentage Completions based on check box

Smartsheet User

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

Mark Cronk

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

Smartsheet User

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

Mark Cronk

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.

Screenshot_20210103-192114_Samsung Internet.jpg

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

Smartsheet User

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

Mark Cronk

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