Repeat Repair Rate: Find the Hidden Capacity Drain in Your Service Department

Repeat Repair Rate by Month

1. Create the three helper columns in the dataset

• Add a Month column for the original job using the service date:
  
  • Use TEXT(ServiceDateCell,”mmmm”) and fill down.
    
• Add a Prior Job Date column using the Linked Prior Job ID:
  
  • If Linked Prior Job ID is blank, return blank.
    
  • If not blank, look up the prior job’s service date by matching that Job ID back to the table (lookup the service date tied to the prior job ID).
    
• Add a Days Since Prior Job column:
  
  • If Linked Prior Job ID is blank, return blank.
    
  • If not blank, calculate the difference between the current arrival time/date and the prior job date to get a day count.
    

2. Build the monthly summary table

• Create a Month list (January through December).
  
• Add column headers:
  
  • Count
    
  • Repair
    
  • Emergency
    
  • Repeat Repair Rate
    
  • 3-Month Average
    

3. Count repeat repairs for each month using COUNTIFS

• Repeat repairs are jobs where:
  
  • Month matches (January, February, etc.)
    
  • Job Status equals Completed
    
  • Days Since Prior Job is less than 60 (or your chosen threshold)
    
• Use COUNTIFS() with criteria:
  
  • Month = the month in your summary row
    
  • Job Status = “Completed”
    
  • Days Since Prior Job = “<=60” (entered as a quoted criteria string)
    

4. Count total completed Repair and Emergency jobs for each month

• For the denominator, count the total “universe” of completed jobs for that month in two categories:
  
  • Completed Repair jobs in the month
    
  • Completed Emergency jobs in the month
    
• Use COUNTIFS() again with criteria:
  
  • Job Status = “Completed”
    
  • Month = the month in your summary row
    
  • Call Type = “Repair”
    
• Copy that formula and change the Call Type criteria to “Emergency” to populate the other column.
  

5. Calculate the Repeat Repair Rate

• Compute Repeat Repair Rate as:
  
  • Repeat Repairs ÷ (Repair Jobs + Emergency Jobs)
    
• Format as a percentage (Ctrl + Shift + 5) and fill down.
  

6. Calculate the 3-month rolling average correctly

• Do not average the three monthly percentages.
  
• Use a weighted approach:
  
  • Sum of repeat repairs for the last 3 months ÷ Sum of total jobs for the last 3 months
    
• Build it so when you drag down, the window shifts automatically to the next 3-month period.
  

7. Create the line chart for Repeat Repair Rate and 3-month average

• Select Month plus the two series:
  
  • Repeat Repair Rate
    
  • 3-Month Average
    
• Insert a Line Chart (Insert → Recommended Charts or Line).
  
• Format chart title as “Repeat Repair Rate by Month”.
  
• Optional formatting shown in the video:
  
  • Remove decimal places in percentages via Number formatting.
    
  • Add data labels to the blue line (and optionally remove labels from the orange line if it gets noisy).
    

8. Build the technician by system type heat map

• Create a unique list of Technician names using UNIQUE() on the Technician column.
  
• Create a unique list of System Types using UNIQUE() on the System Type column.
  
• Paste values to remove formulas.
  
• Sort system types alphabetically, then transpose so System Types run across the top.
  

9. Calculate repeat rate for each technician and system type cell

• For each technician-system type intersection, calculate:
  
  • Numerator: count of repeat repairs (Days < 60, Completed) for that technician and system type, limited to Call Type Repair or Emergency
    
  • Denominator: count of all completed jobs for that technician and system type (Repair or Emergency), without the Days < 60 filter
    
• Use COUNTIFS() with multiple criteria:
  
  • Technician = row label
    
  • System Type = column header
    
  • Job Status = “Completed”
    
  • Call Type = Repair or Emergency
    
  • Days Since Prior Job < 60 (numerator only)
    

10. Use the curly bracket trick to include Repair and Emergency in one criteria

• Instead of writing two COUNTIFS blocks, use an array criteria:
  
  • {“Repair”,”Emergency”}
    
• Wrap the COUNTIFS in SUM() to combine both results into a single numerator or denominator.
  

11. Fix copy behavior with absolute and relative references

• Before copying across the heat map, lock references properly:
  
  • Lock the technician column reference so it doesn’t shift when copying across.
    
  • Lock the system type row reference so it doesn’t shift when copying down.
    
• Copy and paste across the table instead of dragging, so the table structure stays aligned.
  

12. Handle divide-by-zero and missing combinations with IFERROR

• Wrap the full rate calculation in IFERROR():
  
  • If denominator is zero, return blank.
    
• Keep true zeros as zeros (meaning jobs exist, but none were repeats), but hide errors where no jobs exist at all.
  

13. Add conditional formatting with a benchmark threshold

• Set a benchmark cell (example in the video: 25%).
  
• Apply conditional formatting:
  
  • Highlight Cell Rules → Greater Than → 25%
    
• Add a second rule to format blanks:
  
  • New Rule → Format only cells that contain blanks
    
• In Manage Rules, check “Stop If True” for the blank rule so blanks don’t get colored by other rules.