How to Track Average Project Duration in Excel
for Better Install Planning

How to Calculate Average Project Duration (Days) in Excel

1. Add the Actual Duration Column

• Start with a dataset that includes:
  • Project ID
  • Start Date
  • Actual End Date
  • System Type
  • Project Manager
• Add a new column for Actual Duration.
• Calculate the number of days between the actual end date and the start date.
• Make sure the result is formatted as a number, not as currency.

This creates the base field that will be used for all averages in the report.

2. Convert the Data into a Table

• Select the dataset and turn it into an Excel table using Ctrl + T.
• Using a table makes it easier to reference columns by name and build formulas more efficiently.
• Once the table is set up, all the key fields become easier to use in functions and summaries.

3. Create a Unique List of System Types

• Use the UNIQUE function on the System Type column.
• This creates a distinct list of all system types in the dataset.
• Sort the list if you want it alphabetized.
• Copy the results and paste them as values using Ctrl + Shift + V.

This list will be used as the row labels in the final layout.

4. Create a Unique List of Project Managers

• Use the UNIQUE function on the Project Manager column.
• Sort the names if needed.
• Use the TRANSPOSE function so the project managers appear across the top row instead of down a column.
• Copy the final result and paste it as values.

This creates the column headers for the matrix.

5. Build the Main Average Duration Matrix

• Use the AVERAGEIFS function to calculate the average project duration.
• Set the average range to the Actual Duration column.
• Use System Type as one criterion.
• Use Project Manager as the second criterion.
• Fill in the first intersection cell, then prepare it to be copied across the matrix.

This gives you the average project duration by both PM and system type.

6. Add the Overall Average for All Projects

• In the total section, use the AVERAGE function on the full Actual Duration column.
• This gives you the overall average duration across the entire dataset.

This overall number provides baseline context for the rest of the breakdown.

7. Add the Overall Average by Project Manager

• Copy the existing setup and simplify it so it only filters by Project Manager.
• Keep the duration column as the average range.
• Remove the system type condition.
• Repeat across the row for all project managers.

This shows the average project duration for each PM regardless of system type.

8. Add the Overall Average by System Type

• Copy the setup again and simplify it so it only filters by System Type.
• Remove the project manager condition.
• Repeat down the list for all system types.

This shows the average project duration for each system type regardless of who managed it.

9. Fix the Cell References Before Copying Across the Matrix

• Adjust the references so Excel keeps the correct row and column headers in place when the formula is copied.
• Use absolute and mixed references to lock:
  • The system type reference correctly across columns
  • The project manager reference correctly down rows
• Once the references are set properly, copy and paste the formula across the full matrix.

This ensures each cell calculates the correct average for its PM and system type combination.

10. Add Project Counts for More Context

• Create a second supporting view using the COUNTIFS function.
• Count how many projects exist for each PM and system type combination.
• Remove the duration value column from the logic and count records instead.

This helps you avoid over-interpreting averages based on a very small number of projects.

11. Apply Conditional Formatting the Right Way

• Highlight only the comparable cells within each row.
• Apply Conditional Formatting with Color Scales.
• Do not apply one single color scale to the entire block if you want row-level comparison.
• Use Format Painter to quickly copy the same formatting across the other rows.

This makes it easier to spot:

• higher average durations
• lower average durations
• patterns by PM
• patterns by system type

12. Review the Final Layout for Insights

By the end of the setup, you will have:

• an overall average project duration
• an average by project manager
• an average by system type
• a detailed matrix by project manager and system type
• optional project counts for added context
• conditional formatting to highlight patterns visually

This structure turns the raw project data into a capacity planning view that is much easier to interpret and compare.