Diagnostic Accuracy Dashboard for HVAC:
Track Accuracy by Tech, System Type, and Month

How to Build the Diagnostic Accuracy Rate Dashboard

1. Set Up the Dataset Columns You Need

1. Put your raw service data into an Excel Table
   
   • Select the dataset and press Ctrl + T to convert it to a table
     
   • Using a table makes formulas easier to read because they use structured references
     
2. Add an Accuracy column that compares the initial vs final diagnosis
   
   • In the new column, use an equals comparison between the Initial Description and Final Description
     
   • This returns TRUE or FALSE for each job
     
3. Add a Month column from the service date
   
   • Use the TEXT function on the Date of Service field with the full month format
     
   • Format: TEXT(date, “mmmm”) to return January, February, etc.

2. Build the Monthly Accuracy Summary Table

1. Create a simple month list
   
   • Type Month as the header
     
   • Enter January and drag down to December
     
   • Center the column for readability if desired
     
2. Count total service calls per month
   
   • Use COUNTIF on the table Month column where Month equals the month label in your summary table
     
   • This gives monthly call volume
     
3. Count accurate diagnoses per month
   
   • Use COUNTIFS with two conditions:
     
     • Month equals the month label
       
     • Accuracy equals TRUE
       
4. Count inaccurate diagnoses per month
   
   • Use COUNTIFS again, but set Accuracy equals FALSE

3. Calculate Monthly Diagnostic Accuracy Rate and Add a Benchmark Line

1. Calculate the accuracy percentage
   
   • Accuracy % = Accurate Calls ÷ Total Calls
     
   • Either divide by the total calls column, or divide by Accurate + Inaccurate
     
2. Format as a percent
   
   • Use Ctrl + Shift + 5
     
3. Create a benchmark value cell
   
   • Enter a benchmark like 85% in a separate cell
     
4. Make the benchmark dynamic for charts
   
   • In the benchmark row next to each month, reference the benchmark cell so it repeats across all months
     
   • This makes the benchmark line move when you change the benchmark value

4. Create the Monthly Combo Chart with a Secondary Axis

1. Highlight the Month and Service Calls columns, then include the Accuracy and Benchmark series
   
   • You can select non-adjacent columns by holding Ctrl while selecting
     
2. Insert a Combo Chart
   
   • Insert → Recommended Charts → Combo
     
3. Assign chart series correctly
   
   • Service Calls as columns on the primary axis
     
   • Accuracy % as a line on the secondary axis
     
   • Benchmark as a line on the secondary axis
     
4. Format the benchmark line as dotted
   
   • Copy the benchmark series and paste it into the chart if needed
     
   • Then format the line style to dotted so it reads as a target threshold
     
5. Add data labels to the accuracy line if you want
   
   
   • Click the line → Data Labels
     
   • This keeps the chart readable without cluttering the columns

5. Build the Technician by Diagnosis Group Heat Map

1. Create a unique list of technicians
   
   • Use SORT(UNIQUE(Table[Technician Name]))
     
   • Copy and paste values to remove formulas
     
2. Create a unique list of final diagnosis groups
   
   • Use UNIQUE on the Final Diagnosis Group field
     
   • Sort if desired
     
3. Transpose the diagnosis group list across the top
   
   • Copy the list → Paste Special → Transpose
     
4. Calculate accuracy in each grid cell using COUNTIFS divided by COUNTIFS
   
   • Numerator: COUNTIFS for Technician, Diagnosis Group, and Accuracy TRUE
     
   • Denominator: COUNTIFS for Technician and Diagnosis Group only
     
   • Format results as percent
     
5. Fix dragging issues with absolute references
   
   • Lock the technician reference so it changes when dragging down but not across
     
   • Lock the diagnosis group reference so it changes when dragging across but not down
     
   • Use dollar signs to control what stays fixed
     
6. If table references fight you while dragging
   
   • Copy the formula once, then paste into the matrix range so Excel fills it consistently

6. Add Overall Accuracy Rows and Columns

1. Overall accuracy by technician
   
   • Copy the grid formula and remove the diagnosis group condition
     
   • This returns overall TRUE ÷ total for each technician
     
2. Overall accuracy by diagnosis group
   
   • Copy the grid formula and remove the technician condition
     
   • This returns overall TRUE ÷ total for each group

7. Apply Conditional Formatting for the Benchmark

1. Highlight the full heat map area
   
2. Home → Conditional Formatting → Highlight Cell Rules → Less Than
   
3. Enter your threshold (example: 75) and apply a red format
   
4. If you want the benchmark cell to display text like “Benchmark: 85%” while staying numeric
   
   • Type 85 in the cell
     
   • Press Ctrl + 1 → Custom format
     

Use a custom format that displays the word Benchmark while keeping the value numeric