A tiered commission formula explained

Writes an Excel commission formula for the specified tiers and explains its parts with a worked example.

What you send

Write a copyable Excel formula to calculate tiered commission from B2, then explain each part and give a worked example for EGP 120,000 in sales. Present a short document with the formula, explanation and example, rounding to two decimal places.

I am Khaled, an accountant preparing the October 2026 commission sheet. Cell B2 holds Sara’s monthly sales in EGP: 120000. Internal policy: 2% commission on the first EGP 50,000, 3% on the portion above 50,000 up to 100,000, and 5% on the portion above 100,000. Each rate applies only to its tier, rather than total sales. Sales are non-negative numbers, and commission is rounded to two decimal places.

What you get

October 2026 Commission Sheet — Tiered Commission Formula

Copyable Excel formula

=ROUND(0.02*MAX(0,MIN(B2,50000))+0.03*MAX(0,MIN(B2,100000)-50000)+0.05*MAX(0,B2-100000),2)

This formula assumes B2 contains Sara’s monthly sales in EGP, for example 120000.

Explanation of each part

Formula part Meaning
MIN(B2,50000) Takes the sales amount, but not more than EGP 50,000. This calculates the portion falling in the first tier.
MAX(0,MIN(B2,50000)) Ensures the first-tier amount is never negative. Since sales are non-negative, this is mainly for safety.
0.02*... Applies the 2% commission rate to the first EGP 50,000.
MIN(B2,100000)-50000 Calculates the portion of sales above EGP 50,000, but not above EGP 100,000.
MAX(0,MIN(B2,100000)-50000) If sales are below EGP 50,000, this returns 0 instead of a negative number.
0.03*... Applies the 3% commission rate to the portion from EGP 50,001 up to EGP 100,000.
B2-100000 Calculates the portion of sales above EGP 100,000.
MAX(0,B2-100000) If sales are below EGP 100,000, this returns 0 instead of a negative number.
0.05*... Applies the 5% commission rate to the portion above EGP 100,000.
ROUND(...,2) Rounds the final commission to two decimal places.

Worked example: EGP 120,000 in sales

If B2 = 120000, the commission is calculated as follows:

Tier Portion of sales Rate Commission
First EGP 50,000 50,000 2% 1,000.00
Next EGP 50,000 50,000 3% 1,500.00
Above EGP 100,000 20,000 5% 1,000.00
Total commission 3,500.00

Result

3,500.00 EGP

How it works

  1. Provide the commission tiers and sales cell.
  2. Review the formula, explanation and worked example.
  3. Try the formula with values at the tier boundaries in your sheet.

Related use cases