Average Jobs Per Day by Technician and Month in Excel

Build Jobs Completed per Technician per Day

1. Create the Month List

• In a new area of the sheet, type Month as the header.
  
• Type January in the first cell under it.
  
• Drag down to populate through December.
  
• Center the month labels (optional) to match the layout.
  

2. Calculate Average Jobs Per Day by Month

• Add a header like Average Jobs Per Day next to the month list.
  
• Use AVERAGEIFS() to calculate the average jobs completed for each month:
  
  • Average range: Jobs Completed values
    
  • Criteria range: Month column in the dataset
    
  • Criteria: The month name cell (January, February, etc.)
    
• Copy the formula down for all months (drag or double-click fill handle).
  

3. Add Minimum and Maximum Threshold Inputs

• Add two cells labeled Minimum and Maximum.
  
• Enter example values (like 4.0 for minimum and 5.5 for maximum).
  
• Create two helper columns aligned with the month list:
  
  • In the Min helper column, reference the Min cell using =MinCell
    
  • In the Max helper column, reference the Max cell using =MaxCell
    
• Copy both helper formulas down so the threshold lines can be plotted across all months.
  

4. Create the Monthly Chart with Upper and Lower Bounds

• Select the month list, the average jobs per day column, and the two threshold helper columns.
  
• Go to Insert → Recommended Charts and choose the line chart option.
  
• Update the chart title to something like Average Jobs Per Day by Month.
  

5. Format the Chart for Readability

• Set the chart Y-axis minimum (example used: 3) so the chart is not anchored at zero.
  
• Format decimals to one decimal place for consistency.
  
• Make the threshold lines thinner than the main (blue) average line.
  
• Add data labels to the blue line:
  
  • Turn on data labels
    
  • Format labels to one decimal place
    

6. Build a Unique Technician List

• In a new section, create a header like Technician Name.
  
• Use UNIQUE() on the Technician Name column to generate a de-duplicated list.
  
• Copy the results and paste values (Ctrl + Shift + V) to remove the formula.
  

7. Add Months Across the Top for the Heat Map Grid

• Across the top row of the technician table, add month headers (January through December).
  
• Fast method shown:
  
  • Copy your month list
    
  • Use paste options to transpose (or paste across as shown in the video shortcut)
    

8. Calculate Jobs Per Day by Technician and Month

• In the first grid cell (first technician, January), use AVERAGEIFS() with two criteria:
  
  • Average range: Jobs Completed values
    
  • Criteria range 1: Technician Name column
    
  • Criteria 1: Technician name cell for that row
    
  • Criteria range 2: Month column
    
  • Criteria 2: Month header cell for that column
    
• Fix the formula so it can be dragged across and down:
  
  • Lock dataset columns with F4 as needed
    
  • Lock the technician reference so it changes when dragged down, but not across
    
  • Lock the month reference so it changes when dragged across, but not down
    
• Copy the formula across all months and down all technicians.
  

9. Create the Heat Map Threshold Trigger

• Add a single threshold input cell (example used: 5).
  
• Select the entire technician-by-month grid.
  
• Apply conditional formatting:
  
  • Home → Conditional Formatting → Highlight Cells Rules → Less Than
    
  • Reference the threshold cell so the heat map updates when the threshold changes
    

10. Add “Jobs per Day” Text Without Breaking the Trigger

• Click the threshold cell and press Ctrl + 1 to open Format Cells.
  
• Go to Custom number format.
  
• Use a format like:
  
  • 0.0 “jobs per day”
    
• This keeps the cell numeric for conditional formatting, while displaying the words.