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

Christine Berger (Walsh)

Hello,

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!!

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.

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

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!

Jaykel

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.