Overall Status as a percent score (factoring in percent complete, weighted average and due dates)



I'm trying to calculate a department's overall status based on a percent score. I think I need to use percent of completed deliverables, weighted average of deliverables and deliverable due dates within that area. Any words of wisdom?

I'm working on a project with various departments that each provide an arbitrary status of either "On Track," "Off Track" or "At Risk." My team would like to construct a formula that will yield a certain percent that we can then translate ourselves to a mathematically-founded status. Similar to a grade scale (70-100% is an On Track, 40-69% is Off Track, etc.) or a RAID log scale.

Each department is listed as a parent row and has child rows/tasks that roll up. The percent complete, weighted average and due dates are accounted for. Is it possible to calculate this? Thank you!!


Best Answer


  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Christine Berger (Walsh)

    Hope you are fine if it's possible could you share me as an admin on a copy of your sheet contains sample data (after removing or replacing any sensitive information). and i will create the formula for you using the same column names then you can copy it if it's work and paste it in your main sheet.

    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Jaykel Torres

    Hey @Christine Berger (Walsh),

    It may be helpful to get an average from all the variables by turning them into a percentage. In your scenario, it looks like most are already percentages except the start/end date. By utilizing the SUM, TODAY, NETDAYS, Nested IF Functions and simple math, you may be able to get an overall average. I created the example below on how this may look like:


    Formula: =IF(SUM([%a]@row:[%c]@row) / 3 = 1, 1, IF(SUM([%a]@row:[%c]@row) / 3 = 0, 0, IF(startDate@row > TODAY(), SUM([%a]@row:[%c]@row) / 3, SUM([%a]@row:[%c]@row) + (NETDAYS(startDate@row, TODAY()) / NETDAYS(startDate@row, endDate@row))) / 4))

    This formula's actions are as follows:

    • If the Sum of % a, b, and c divided by 3 is equal to 100%, return 100%
    • If the Sum of % a, b, and c divided by 3 is equal to 0%, return 0%
    • If the Start Date is greater than Today's date, return the Sum of % a, b, and c divided by 3
    • If the Start Date is less than Today's date, return the Sum of % a, b, c and (elapsed days/total days) divided by 4
      • This takes into account of the start/end date and elapsed days so far

    Please note, you can also use the AVG Function instead of the SUM Function, but you may need to use the IFERROR Function to handle the #DIVIDE BY ZERO error.

    I hope this helps!


  • Christine Berger (Walsh)

    @Jaykel T. @Bassam Khalil thank you both and apologies for the delay. This function did not give me what I was trying to find, but I reconstructed my sheet to go about it a different way. I appreciate it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!