Complex commission structure
AdvancedCommission structures in sales organizations often combine multiple rules: tiered rates reward higher performers, team bonuses encourage collaboration, and caps control payroll costs. Building a formula that handles all three requires combining nested IF, MIN, and conditional logic.
In this sheet, you have seven sales reps with their monthly sales in column B. The commission rules are defined in the reference tables on the right:
Commission tiers (E2:G4): Base commission rate depends on individual sales level. Higher sales earn a higher rate.
Team bonus (E6:F8): If the team total (cell B10) exceeds the team target, each rep receives an additional bonus rate applied to their individual sales.
Commission cap (F10): No rep can earn more than this amount, regardless of their calculated commission.
Your task:
Write a formula in C2 that calculates the final commission for Jordan Lee, incorporating:
- The correct tiered base rate from the tier table
- The team bonus (if the team target is met)
- The cap as a maximum limit
Fill the formula down through C8 for all reps. Use absolute references for the lookup values so the formula copies correctly.
Need some help?
Hint 1
Start with the tiered base commission using nested IF: check the highest tier first (25,000), then the middle tier (10,000), with the lowest tier as the fallback.
Hint 2
Add the team bonus with a separate IF that checks if B10 meets the team target. The bonus applies to each rep's individual sales, not the team total.
Hint 3
Wrap the entire calculation in MIN to apply the cap. Your structure should be: MIN(base_commission + bonus_if_applicable, cap).
Related function(s)
Answer
Exercise
Complex commission structure
AdvancedCommission structures in sales organizations often combine multiple rules: tiered rates reward higher performers, team bonuses encourage collaboration, and caps control payroll costs. Building a formula that handles all three requires combining nested IF, MIN, and conditional logic.
In this sheet, you have seven sales reps with their monthly sales in column B. The commission rules are defined in the reference tables on the right:
Commission tiers (E2:G4): Base commission rate depends on individual sales level. Higher sales earn a higher rate.
Team bonus (E6:F8): If the team total (cell B10) exceeds the team target, each rep receives an additional bonus rate applied to their individual sales.
Commission cap (F10): No rep can earn more than this amount, regardless of their calculated commission.
Your task:
Write a formula in C2 that calculates the final commission for Jordan Lee, incorporating:
- The correct tiered base rate from the tier table
- The team bonus (if the team target is met)
- The cap as a maximum limit
Fill the formula down through C8 for all reps. Use absolute references for the lookup values so the formula copies correctly.
Need some help?
Hint 1
Start with the tiered base commission using nested IF: check the highest tier first (25,000), then the middle tier (10,000), with the lowest tier as the fallback.
Hint 2
Add the team bonus with a separate IF that checks if B10 meets the team target. The bonus applies to each rep's individual sales, not the team total.
Hint 3
Wrap the entire calculation in MIN to apply the cap. Your structure should be: MIN(base_commission + bonus_if_applicable, cap).