Calculate tiered sales commission (IF)
Quarter-end is here and sales ops needs commission run for the team in B2:B5. The plan has three tiers: 8% on sales of at least 10,000, 5% on sales of at least 5,000, and 3% below that. Fill the Commission cells D2:D5 with each person's payout.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
Nested IFs evaluate top-down, so test the highest tier first — otherwise a 12,000 sale would fall into the ≥5,000 branch and be paid 5%. Watch the boundary: exactly 10,000 belongs to the 8% tier.
Function guide
FAQ
How do I calculate tiered commission in Excel?
Nest one IF inside another: =IF(B2>=10000, B2*0.08, IF(B2>=5000, B2*0.05, B2*0.03)). Each level catches one tier and the last value is the fallback rate.
Why test the highest tier first?
IF stops at the first true condition. A 12,000 sale also satisfies >=5000, so if that test came first it would wrongly pay 5%. Always order thresholds from high to low.
Is there a flatter alternative to nested IF?
Yes — IFS (Excel 2019+/365): =IFS(B2>=10000, B2*0.08, B2>=5000, B2*0.05, TRUE, B2*0.03). Same logic, easier to read; older versions need the nested form.