Daily Percentage Completions based on check box

Options


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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/02/21
    Options

    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


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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



  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.