Formulate % Complete for Stages on Dashboard
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!
Arielle Priday, Project Administrator
Prism Professional Services Group
4340 W. Chandler Blvd  Ste 8  Chandler, AZ 85226
Answers

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.

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?
Help Article Resources
Categories
Check out the Formula Handbook template!