Contact Attempts per Lead (Average):
Weekly Trend + Sales Rep Breakdown

Contact Attempts per Lead (Average) in Excel

1. Set up the dataset columns you need

• Create a dataset that includes at minimum:
  
  • Lead ID
    
  • Lead created date/time
    
  • Sales rep
    
  • Attempt number
    
  • Attempt date/time
    
  • Lead source
    
• Keep the data in an Excel Table format to make formulas and references easier to manage.
  

2. Create a unique Lead ID list for modeling

• Use the UNIQUE function on the Lead ID column in your table to generate one row per lead.
  
• Copy the results and paste values (Ctrl + Shift + V) so you are working with fixed IDs.
  

3. Bring in the lead created date for each Lead ID

• Use INDEX + MATCH (or XLOOKUP) to return the created date/time for each Lead ID.
  
• Format the returned value as Short Date if the time shows as a decimal.
  
• Autofill down to populate the created date for all Lead IDs.
  

4. Add the week number for each lead

• Use WEEKNUM on the created date to assign a week-of-year value.
  
• Fill the formula down for all Lead IDs.
  

5. Bring in Lead Source and Sales Rep for each Lead ID

• Use a lookup method (VLOOKUP shown in the video) to pull:
  
  • Lead Source
    
  • Sales Rep
    
• Use exact match logic (the FALSE or 0 argument).
  
• Center-align the table for readability if desired.
  

6. Calculate contact attempts per lead using MAXIFS

• Instead of counting rows per Lead ID, use MAXIFS on the Attempt Number:
  
  • Return the maximum attempt number for each Lead ID
    
• This gives the number of attempts for that lead, since attempts are numbered sequentially.
  

7. Build the weekly summary table

• Create a small weekly table with these columns:
  
  • Week Number
    
  • Week Start
    
  • Volume
    
  • Average
    
  • Target
    

8. Generate a unique list of week numbers

• Use UNIQUE on the week number column from your modeled lead table.
  
• This gives you one row per week for trend reporting.
  

9. Calculate the week start date using MINIFS

• Use MINIFS to return the minimum created date for each week number.
  
• Format as a date and fill down.
  

10. Calculate weekly lead volume with COUNTIFS

• Use COUNTIFS to count how many leads occurred in each week number.
  
• Fill down the full list of weeks.
  

11. Calculate weekly average attempts with AVERAGEIFS

• Use AVERAGEIFS to average the attempts-per-lead column for each week number.
  
• If using explicit ranges (not full columns), lock the ranges using absolute references (F4) so they do not shift when filling down.
  
• Format the average cleanly (video uses a number format shortcut like Ctrl + Shift + 1).
  

12. Add a dynamic target line

• Enter a target value (example used: 3.5).
  
• Create a target column that equals the target cell, copied down the entire weekly table so the chart displays a constant target line.
  
• This makes the target easy to change later and instantly updates the chart.
  

13. Create the weekly combo chart

• Select the weekly table (Week Start through Target).
  
• Insert a Combo chart:
  
  • Volume as clustered columns
    
  • Average as a line
    
  • Target as a line
    
• Put Average and Target on the secondary axis so they are readable next to volume.
  
• Adjust colors and styling as needed (example: make the target line gray, thinner, and dashed).
  

14. Build the Sales Rep heat map table

• Create a unique list of Sales Reps using UNIQUE and paste values.
  
• Create a unique list of Lead Sources:
  
  • Use UNIQUE, optionally wrap with SORT to alphabetize
    
  • Paste values
    
• Transpose the Lead Sources across the top row so Sales Reps are listed down the left and Lead Sources run across columns.
  

15. Calculate the heat map values with AVERAGEIFS

• In the grid, use AVERAGEIFS to return average attempts where:
  
  • Sales Rep equals the row header
    
  • Lead Source equals the column header
    
• Lock references correctly with F4:
  
  • Lock the attempts range
    
  • Lock the sales rep column reference (so it doesn’t shift when dragged across)
    
  • Lock the lead source row reference (so it doesn’t shift when dragged down)
    
• Drag the formula across and down to fill the matrix.
  

16. Add conditional formatting against a target

• Put a target value beneath the grid (example used: 4).
  
• Apply Conditional Formatting:
  
  • Highlight Cells Rules
    
  • Less Than
    
  • Reference the target cell (absolute referencing added automatically)
    
• Choose a formatting style (video example: red) to flag underperformance.
  

17. Display the word “Target” in the target cell without breaking math

• Do not type “Target 3.5” directly into the cell.
  
• Instead, format it using a custom number format:
  
  • Format Cells (Ctrl + 1)
    
  • Custom
    
  • Add a format like: Target: 0.0
    
• This keeps the cell numeric for comparisons and charting, while showing a label for humans.