Formulate % Complete for Stages on Dashboard

PrismPSG

Overview: I have a sheet with a number of equipment items for my team to collect and log. The data is split into 3 stages. (Stage 1 = Sky blue, Stage 2 = Medium blue, Stage 3 = Grey)

If the data has not yet been gathered the column with populate a 'N'. The project team will change the cell to 'Y' if field is complete, or 'N/A' if not applicable for that piece of equipment.

Stage 1 has (5) fields and makes up 60% of the completion for that row of data.

Stage 2 has (7) fields and makes up 30% of the completion for that row of data.

Stage 3 has (2) fields which makes up 10% of the completion for that row of data.

The end of each phase is a column with a check box, indicating the overall completion of that phase.

Need: I need to populate a graph/chart on a dashboard that shows the shows the project % complete based of the total # of rows and how far along each row is within phases 1,2, and 3.

(See snippet of practice sheet below which has 24 items in the project, 3 rows are 100% complete, 5 rows are 90% complete, 1 row is 60% complete, 15 rows are under <60% complete.

Thank you!

Leibel S

@PrismPSG

Lets start with the data, if you are looking for a percentage complete for each row, try this (you may need to fix the column names in here)

=SUM(SUM(COUNTIFS([Field Check and Equipment Data Gathered]@row:[Cable Code Check]@row, OR(@cell = "Y", @cell = "N/A")) * (60 / 5)) + SUM(COUNTIFS([Ard Flash analysis complete]@row:[Report reviewed]@row, OR(@cell = "Y", @cell = "N/A")) * (30 / 7)) + SUM(COUNTIFS([Label Generated]@row:[Label applied/Delivered]@row, OR(@cell = "Y", @cell = "N/A")) * (10 / 2))) / 100

Please note for this formula to work you need to keep the layout in the sheet as it is now (you cannot move around the columns)

I am not sure of exactly what kind of chart you want to show but i hope this helps.

BullandKhmer

I could ask 1000 questions here, but I wont.

To start with you need a % complete column with a column formula probably a nestled IF formula.

Then I'd use a summary field to calculate the overall percentage complete of the project. Assuming all rows are an equal amount of work it would simply be =SUM([%complete]:[%complete])/COUNT([%complete]:[%complete])

Finally I don't see how overall % complete is a chart, its just a metric right?