Nested IF for commission tiers
IntermediateSales commissions are often calculated using a tiered rate: higher-performing reps earn a higher commission rate. A nested IF is a practical way to apply those tier rules in a single formula.
In this sheet, monthly sales are listed in column B, and the commission tier thresholds are in columns E and F.
Your task:
- In cell C2, write a nested IF that calculates the commission amount for the sales in B2.
- Use the tier table (E2:F4) for the thresholds and rates, and lock those references so you can fill down.
- Fill the formula down through C9.
Need some help?
Hint 1
Start by testing the highest tier first (the 25,000 minimum), then the middle tier, and use the lowest tier as the final ELSE case.
Hint 2
Use absolute references for the tier table (like $E$2 and $F$2) so the thresholds and rates don’t shift when you fill down.
Hint 3
If you return a commission amount, your IF outputs should multiply the sales cell (B2) by the correct rate from column F.
Answer
Exercise
Nested IF for commission tiers
IntermediateSales commissions are often calculated using a tiered rate: higher-performing reps earn a higher commission rate. A nested IF is a practical way to apply those tier rules in a single formula.
In this sheet, monthly sales are listed in column B, and the commission tier thresholds are in columns E and F.
Your task:
- In cell C2, write a nested IF that calculates the commission amount for the sales in B2.
- Use the tier table (E2:F4) for the thresholds and rates, and lock those references so you can fill down.
- Fill the formula down through C9.
Need some help?
Hint 1
Start by testing the highest tier first (the 25,000 minimum), then the middle tier, and use the lowest tier as the final ELSE case.
Hint 2
Use absolute references for the tier table (like $E$2 and $F$2) so the thresholds and rates don’t shift when you fill down.
Hint 3
If you return a commission amount, your IF outputs should multiply the sales cell (B2) by the correct rate from column F.