Math & Trig
Project completion percentage: task count vs effort-weighted progress
Avoid false progress spikes on small chores by weighting each task's % complete by budgeted hours with SUMPRODUCT.
Why unweighted task counts overstate project readiness
Suppose three 2-hour setup tasks are 100% done while a 34-hour core engineering milestone is only 25% done. Counting completed tasks (3 / 4 = 75%) or averaging task percentages (81.25%) hides the fact that most labor hours remain unfinished.
Effort-weighted progress divides earned hours SUMPRODUCT(EstHours, PctComplete) by total budgeted hours SUM(EstHours): (2*1 + 2*1 + 2*1 + 34*0.25) / 40 = 14.5 / 40 = 36.25%.
| Task (A) | EstHours (B) | PctDone (C) | Status (D) | Earned Hours |
|---|---|---|---|---|
| Kickoff | 2 | 1 | Done | 2.0 |
| Wireframes | 2 | 1 | Done | 2.0 |
| Repo Setup | 2 | 1 | Done | 2.0 |
| Core Engine | 34 | 0.25 | Active | 8.5 |
Effort-weighted % complete and simple status count %
Guard SUM(B2:B5)=0 so blank project templates display 0 instead of #DIV/0!.
=IF(SUM(B2:B5)=0, 0, ROUND(SUMPRODUCT(B2:B5, C2:C5) / SUM(B2:B5), 4))Unweighted checklist completion %
Use COUNTIF / COUNTA only when all checklist items have roughly equal effort.
=COUNTIF(D2:D5, "Done") / COUNTA(A2:A5)Inputs and interpretation
Use nonnegative numeric estimates and completion fractions from 0 to 1 for each named task. Zero total estimate produces a chosen display placeholder of zero, not a mathematically defined weighted mean. Earned/remaining budgeted hours are not measurements of actual hours worked or a forecast of hours still required. The checklist ratio assumes every status belongs to a populated task and at least one task exists.
Try the example
Use the original values or change them to test your understanding.