Stop Using Averages:
Track True Supplier Lead Time in Excel

How to Calculate Median Lead Time (Days) in Excel

1. Set up the two helper columns

• Start with the key fields needed for the analysis: order date, receipt date, and supplier.
• Create a Month End column using the EOMONTH function so each order is assigned to the end of its order month.
• Create an Actual Lead Time Days column by subtracting the order date from the receipt date.
• Format the month-end column as a date so it displays correctly.

2. Build a unique list of month-end values

• Use the UNIQUE function on the Month End column to remove duplicates.
• Format the resulting list as dates.
• Copy and paste the results as values so the list stays fixed for the report area.

3. Build a unique list of suppliers

• Use the UNIQUE function again, this time on the Supplier column.
• Sort the supplier list alphabetically to make the heat map easier to scan.
• Use Transpose so the supplier names run across the top row instead of down a column.

4. Filter the data for each month and supplier combination

• In the report area, use the FILTER function to return only the lead-time records that match:
  • the selected month-end value
  • the selected supplier
• Use both conditions together so the results only include rows that match that exact combination.
• The video uses multiplication inside the filter logic to apply an AND condition.

5. Wrap the filtered results in a median calculation

• Once the filtered list of lead times is returned, wrap it with the MEDIAN function.
• This gives the median lead time for each supplier by month.
• Fill the calculation across and down to populate the full matrix.

6. Lock the cell references correctly

• Adjust the row and column references so the formula copies properly:
  • the month reference should stay locked to the correct column while changing by row
  • the supplier reference should stay locked to the correct row while changing by column
• This keeps the matrix aligned as you fill the formula through the full grid.

7. Create the overall median by month

• Copy the main calculation and simplify it so it only filters by month.
• Remove the supplier condition from the logic.
• Fill the formula down to create a summary column showing the overall median lead time for each month.

8. Create the overall median by supplier

• Copy the main calculation again and simplify it so it only filters by supplier.
• Remove the month condition from the logic.
• Fill the formula across to create a summary row showing the overall median lead time for each supplier.

9. Calculate the overall median lead time target

• Use the MEDIAN function directly on the full Actual Lead Time Days column.
• This gives you the overall benchmark for the dataset.
• Place that value in a clearly visible target cell for the heat map.

10. Apply conditional formatting to the heat map

• Highlight the matrix of median lead times.
• Use Conditional Formatting with a Greater Than rule.
• Reference the target value so any supplier-month result above the threshold is highlighted.
• This turns the matrix into a heat map that quickly shows slower lead-time areas.

11. Make the target cell easier to understand

• Keep the target cell numeric so conditional formatting continues to work.
• Use Format Cells with a Custom Number Format so the displayed value includes the word Target while still functioning as a number.
• This helps coworkers understand that the cell is editable and drives the heat map logic.

12. Finish the report formatting

• Format the results to one decimal place if needed.
• Clean up headers and alignment so the matrix is easy to read.
• The finished layout shows:
  • median lead time by month and supplier
  • overall median by month
  • overall median by supplier
  • a dynamic target-based heat map