How to Calculate Average Commission Rates by Carrier and Product Line in Excel

Average Commission Rate by Carrier, Month, and Product Line in Excel

This walkthrough shows how to calculate average commission rates by carrier and month for a selected product line, build dynamic dropdowns, and connect everything to an updating chart in Excel.

1. Build a Carrier List With UNIQUE and TRANSPOSE

1. Start with your raw data table that includes:
   
   • Carrier (column J in the example)
     
   • Product Line
     
   • Commission Amount
     
   • Written Premium
     
   • Month (or a date you can turn into a month)
     
2. Create a unique list of carriers:
   
   • Type =UNIQUE([Carrier column]) in an empty range.
     
   • This spills a vertical list of all carriers from the dataset.
     
3. Convert the spilled list to fixed values:
   
   • Copy the UNIQUE results.
     
   • Use Paste Special → Values (or Ctrl + C, then Ctrl + Shift + V).
     
4. Transpose carriers so they sit across columns:
   
   • Copy the vertical list.
     
   • Right-click in a new row.
     
   • Choose Paste Special → Transpose.
     
   • Optionally sort the carriers alphabetically before or after transposing.
     

This row of carriers will become your column headers in the commission rate matrix.

2. Create the Month Labels

1. In the first column of your matrix, type the month names:
   
   • January in the first row.
     
   • Drag the fill handle down to December.
     

You now have a grid of Months (rows) by Carriers (columns) ready for formulas.

3. Build a Dynamic Product Line Dropdown

You want the user to pick a product line from a dropdown, and have the whole matrix and chart respond.

1. Create a distinct list of product lines using UNIQUE, similar to carriers.
   
2. If a zero appears at the bottom due to blanks:
   
   • Adjust the UNIQUE reference slightly (for example, adding a period in an adjacent cell technique) so the list ignores that blank row while staying dynamic.
     
3. Convert the UNIQUE output to values with Copy → Paste Values.
   
4. Sort the product lines alphabetically if desired.
   

Turn Product Lines Into a Comma-Separated List With TEXTJOIN

Data validation “List” can use either a range or a comma-separated string. The video uses a single comma-separated cell:

1. Use TEXTJOIN on the product line list:
   
   • Choose comma as the delimiter.
     
   • Set “ignore empty” to TRUE.
     
   • Select the product line range as the text argument.
     
2. Copy the TEXTJOIN result cell and paste as values so it becomes literal text.
   
3. Use this cell as the source for the dropdown:
   
   • Select the cell where you want the product line dropdown.
     
   • Go to Data → Data Validation.
     
   • Choose List.
     
   • Point the Source to your TEXTJOIN cell.
     

Now changing that product line dropdown will control which rows are included in your SUMIFS formulas.

4. Why You Cannot Just Average Row-Level Commission Percentages

Each row in the dataset could calculate a row-level commission rate:

• Commission Amount ÷ Written Premium
  

However, averaging those row percentages does not give the correct overall commission rate, because rows can have very different premium amounts.

The correct average commission rate is:

• Sum of Commission Amount for the group
  divided by
  
• Sum of Written Premium for the same group
  

This is why the matrix uses SUMIFS to aggregate commission and premium first, then divides.

5. Use SUMIFS to Get Total Commission by Carrier, Month, and Product Line

For each cell in the months-by-carrier matrix, you want:

• Total commissions for a specific carrier
  
• In a specific month
  
• For the selected product line
  

Key steps:

1. Use SUMIFS with:
   
   • Sum range: Commission Amount column.
     
   • Criteria range 1: Carrier column.
     
   • Criteria 1: Carrier header cell in the matrix.
     
   • Criteria range 2: Month column.
     
   • Criteria 2: Month row label.
     
   • Criteria range 3: Product Line column.
     
   • Criteria 3: The product line dropdown cell.
     
2. Lock references thoughtfully:
   
   • Lock entire data columns (commission, carrier, month, product line).
     
   • Use mixed references on the matrix headers and month labels so:
     
     • When you drag formulas right, the carrier reference changes but the row stays fixed.
       
     • When you drag formulas down, the month reference changes but the column stays fixed.
       
3. Confirm the result for a single carrier/month/product-line cell to validate logic.
   

At this point, the cell returns the total commission amount for that carrier, month, and product line.

6. Divide by Written Premium to Get the Commission Percentage

Next, you need the matching total Written Premium for the same filter:

1. Copy the existing SUMIFS formula.
   
2. Change the sum range from the Commission column to the Written Premium column.
   
3. Use that formula as the denominator in a division:
   
   • (SUMIFS on Commission) ÷ (SUMIFS on Written Premium).
     

Format the result as a percentage (Ctrl + Shift + 5) and adjust decimal places as needed.

Drag the formula across the entire matrix so all months and carriers have a commission percentage.

7. Use IFERROR to Hide Errors and Clean Up the Matrix

Some carrier/month combinations will have no data. Those cells would produce a divide-by-zero error.

1. Wrap your commission percentage formula in IFERROR:
   
   • IFERROR(commission_formula, “”) to show blanks instead of errors in the matrix.
     
2. Later, when charting, you may switch the second argument to NA() if you want Excel charts to treat missing points as gaps instead of zeros.
   

Now the grid looks clean and is ready for conditional formatting and charts.

8. Add Conditional Formatting for a Heat Map

To visually scan highs and lows:

1. Select the commission percentage matrix.
   
2. Apply Conditional Formatting:
   
   • Color scales to show lower percentages in one color and higher ones in another.
     
3. Every time the product line dropdown changes, SUMIFS recalculates and the heat map updates automatically.
   

9. Build a Secondary Section and Chart by Agency (or Another Dimension)

The video then builds a second dynamic section to chart commission percentage over time for a single agency (or similar dimension):

1. Copy the bottom row or small matrix structure to a new area for the chart source.
   
2. Repeat the TEXTJOIN + data validation method to create a dropdown of agencies.
   
   • TEXTJOIN across the agency names.
     
   • Copy → Paste Values.
     
   • Use Data Validation → List with that cell as the source.
     
3. Point your SUMIFS formulas to:
   
   • The selected agency.
     
   • The selected product line (if still in play).
     
   • Each month as the rows.
     

This gives a single row or column of monthly commission percentages for the selected agency.

10. Create a Dynamic Line Chart

1. Select the monthly commission percentage series for the current agency.
   
2. Insert a line chart or use Recommended Charts.
   
3. Fix the chart title and format axes as desired.
   

Now, when you:

• Change the product line dropdown, the entire heat map and the chart update.
  
• Change the agency dropdown, the line chart updates to show that agency’s pattern.
  

11. Handling Blanks, Zeros, and Chart Gaps

Excel treats blanks and zeros differently in charts:

• Zeros often appear as “valleys” in the line.
  
• Blanks can be configured as gaps or zero lines.
  

In the video, the formula originally used IFERROR(…, “”), which creates blanks. However, to use Excel’s “Show empty cells as gaps” behavior effectively, the trick is:

1. Change IFERROR(…, “”) to IFERROR(…, NA()) for the chart source range.
   
2. Then in the chart:
   
   • Use Select Data → Hidden and Empty Cells.
     
   • Choose how to show empty cells (as gaps, zero, or connected).
     
3. Decide your preferred behavior:
   
   • Blanks in the matrix but valleys on the chart, or
     
   • Visible error codes in the sheet but clean gaps in the line.
     

That design choice depends on how you want the report to look and how much you care about seeing missing data vs zero data.

The end result is a dynamic, dropdown-driven heat map and chart showing average commission rates by carrier, month, and product line.