Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate tiered commission in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=IFS(B2>=10000, B2*0.1, B2>=5000, B2*0.07, TRUE, B2*0.05)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1List the tiers from highest threshold to lowest.
- 2Each pair is a condition and the commission for it.
- 3TRUE at the end applies the base rate to everything below the lowest tier.
Tips
- In older Excel without IFS, use nested IFs or a rate table with VLOOKUP(..., TRUE).
- A lookup table is easier to maintain than a long IFS when you have many tiers.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I handle many commission tiers cleanly?
Put the thresholds and rates in a small table and use VLOOKUP with approximate match (TRUE). It scales better than a long IFS.
More on Finance basics
See all →How-to
How to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
How-toHow to calculate compound interest
Compute a compounded future value with principal × (1 + rate)^years using POWER. Identical in Excel and Google Sheets.
How-toHow to calculate cost per unit
Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.
How-toHow to calculate a selling price from a target margin
Use cost and target gross margin percentage to back-calculate the required selling price in Excel or Google Sheets.
How-toHow to calculate simple interest
Multiply principal, rate, and time to compute simple interest using a straightforward formula in Excel or Sheets.
How-toHow to calculate a loan payment
Use the PMT function to calculate periodic loan payments given a principal, interest rate, and number of periods.