In this article, we are going to show you **5** quick methods to create an **Excel formula** to **calculate sliding scale commission**.

**Table of Contents**hide

## Download Practice Workbook

## What Is Sliding Scale Commission?

Sales employees are a core part of any company. We need to keep them motivated to reach higher sales targets. Money is the biggest motivator in this world. Now, most companies use **sliding scales **to determine **commissions**.

For example, we can observe this data –

**$0**–**$10,000**>>>**10%****$10,001**–**$15,000**>>>**15%****$15,001**–**$35,000**>>>**20%**- More than
**$35,000**>>>**25%**

If an employee generates sales of less than **$10,000**, then he or she will get a **10%** **commission**, and so on. This discrimination encourages employees to achieve more sales.

Further, there are two types of **sliding scales **– the first one is on the whole amount. In this, the employee gets more as they generate more sales. The other one is on the **cumulative **amount.

For instance, if an employee generates **$15,000** then he will get a **15%** **commission **on the whole amount for the first type. However, that employee will get **10%** on the first **$10,000** and **15%** on the remaining **$5000** sales.

From a company’s perspective, they prefer the second type. But, the **calculation **is more complex for this.

## 5 Ways to Calculate Sliding Scale Commission with Excel Formula

To demonstrate our methods we have taken two tables for this article. The first one consists of **3 columns**: “**Name**”, “**Sales**”, and “**Commission**”. Then, the second table also has **3 columns**: “**Lowest**”, “**Highest**”, and “**Percentage**”. Additionally, we will change this dataset throughout our methods. Moreover, we will find cumulative **sliding scale commission **for the first **3** methods and **sliding scales commission **on the whole for the last **2** methods.

### 1. Use of Excel Formula to Create Sliding Scale Commission Calculator

We will use **IF**, **SUM **functions, and some generic formulas for the first method to create a **sliding scale commission calculator**. This method will show the **cumulative sliding scale commission**.

**Steps:**

- To begin with, we have already created a table for our
**calculator**. We have our**sliding scale**on the bottom table. - Next, the number of
**sales**that occurred is provided in**cell E4**.

- Now, we will
**calculate**the**commission**per tier. - So, we typed this formula in
**cell D8**.

`=C8*E8`

- This formula calculates the
**commission**on the sales between**$0**and**$15,000**. - Then, we have typed another formula to
**calculate commission**based on the remaining amount in cell**D9**.

`=(C9-C8)*E9`

- Afterward, we typed this formula in
**cell F8**.

`=E4-C8`

Here, we **calculated **the **surplus **amount by **subtracting** the **Total Sales** amount from the **highest** value of our first **sliding scale**.

- Then, we typed this formula in
**cell F9**.

`=F8-C9`

- Again, we
**subtract**the previous**surplus**value from the**highest**value of the second tier from the**sliding commission**. - Then, we find the
**commission**breakdown per tier using the following three formulas. - To begin with, type this formula in
**cell G8**.

`=IF(E4>C8,D8,E4*E8)`

- This formula checks if our
**Total Sales**value is higher than the**highest**value of the first tier, if yes we would get the value of**$1500**from the**Flat Commission column**. As it is exactly the same, hence we got**10% commission**from the**Total Sales**value. - Secondly, type this formula in
**cell G9**.

`=IF(F8>C8,D9,F8*E9)`

- If the
**cell**value of**F8**is more than**cell**value**C8**, then it will return the value from**cell D9**, else the multiplication of**cells F8**by**E9**will be returned. - Lastly, type another formula in
**cell G10**.

`=IF(F9>0,F9*E10,"")`

- If the
**Surplus**value in**cell F9**is negative, then it will keep the**cell**blank. - The three formulas will look like this.

- Afterward, we
**added**all the**commission**breakdown values to get the**Total Commission**by typing the following formula in cell**E5**.

`=SUM(G8:G10)`

- Lastly, press
**ENTER**.

Thus, we have shown you the first method of **calculating **the **sliding scale commission **in **Excel**.

**Read More: How to Calculate Commission in Excel Using IF Function**

### 2. Using VLOOKUP Function to Calculate Sliding Scale Commission

In this section, we will use **the VLOOKUP function** to calculate the **sliding scale commission**.

**Steps:**

- First, select the
**cell**range**D5:D10**. - Next, type the following formula.

`=VLOOKUP(C5,$B$13:$D$18,2)+(C5-VLOOKUP(C5,$B$13:$D$18,1))*VLOOKUP(C5,$B$13:$D$18,3)`

**Formula Breakdown**

- In this formula, we are using three
**VLOOKUP**functions. Here, we have not set the*range_lookup*method, therefore thewill be used by default.*approximate match* -
**VLOOKUP(C5,$B$13:$D$18,2)****Output: 113.75**.- First, this part looks for the value in
**cell C5**in the**B13:D18**range and returns the value from the second**column**, which is**113.75**.

**VLOOKUP(C5,$B$13:$D$18,3)****Output: 0.035**.- Then, this part looks for the value in
**cell C5**in the**B13:D18**range and returns the value from the third**column**, which is**0.035**.

**C5-VLOOKUP(C5,$B$13:$D$18,1)****Output: 0**.- Afterward, this part looks for the value in
**cell C5**in the**B13:D18**range and returns the value from the third**column**, which is**5000**. The value of**cell C5**is also**5000**. Hence, we get the value**0**.

- Lastly, the formula reduces to ->
**113.75+0*0.035**adding these values we get the value,**113.75**.

- Finally, press
**CTRL+ENTER**.

This will **AutoFill the formula** to the rest of the **cells**.

Therefore, we use an **Excel **formula to **calculate **the **sliding scale commission**.

**Read More:** **How to Calculate Tiered Commission in Excel (3 Easy Methods)**

### 3. Calculate Sliding Scale Commission by Incorporating SUMPRODUCT & IF Functions

For the third method, we will combine **SUMPRODUCT** and **IF** functions to create a formula to **calculate sliding scale commission**.

**Steps:**

- To begin with, we will
**calculate**the**commission**percentage difference. Except, the first value will be the same. - So, select the
**cell**range**E14:E16**and type the following formula.

`=D14-D13`

- After that, press
**CTRL+ENTER**. - So, it will
**calculate**the percentage difference. - Then, select the
**cell**range**D5:D10**. - Afterward, type the following formula.

`=IF(C5>C13,SUMPRODUCT(--(C5>$C$13:$C$16),(C5-$C$13:$C$16),$E$13:$E$16)+C13*D13,C13*D13)`

**Formula Breakdown**

- In this formula we have two main parts – the first one is the
**SUMPRODUCT**function and the second one is the**IF**function. **SUMPRODUCT(–(C5>$C$13:$C$16),(C5-$C$13:$C$16),$E$13:$E$16)+C13*D13****Output: 650**.- First, there are three arrays in this formula. The first part checks if the value from
**cell C5**is greater than how many values from the**cell**range**C13:C16**. Additionally,*we put a double negative in front of this to convert it into a number format*. - Then, we
**subtract**the value from**cell C5**to each**cell**from the same range. - Afterward, we take the percentage difference from the
**sliding commission**table. - Finally, we
**multiply**and**add**these values with the**multiplication**of**cells C13**and**D13**to get the output of**650**.

- Therefore, our formula reduces to ->
**IF(C5>C13,650,C13*D13)**- As the value from,
**cell C5**is greater than that of**cell C13**, it will return the same output as**650**. Else, we would have gotten the value of**C13*D13**.

- As the value from,

- Finally, press
**CTRL+ENTER**.

This will **AutoFill the formula** to the rest of the **cells**.

Therefore, we use an **Excel **formula to **calculate **the **sliding scale commission**.

**Read More:** **How to Calculate Sales Commission Formula in Excel (3 Easy Ways)**

### 4. Merging INDEX & MATCH Functions to Calculate Sliding Scale Commission

Up to this point, we have **calculated **the **cumulative sliding scale commission**. Now, we will find the **sliding scale commission **on the **whole amount**. Moreover, we will use **INDEX** and **MATCH** functions in this method.

**Steps:**

- First, select the
**cell**range**D5:D10**. - Next, type the following formula.

`=INDEX($D$13:$D$16,MATCH(C5,$B$13:$B$16,1))*C5`

**Formula Breakdown**

**MATCH(C5,$B$13:$B$16,1)****Output: 2**.- This function returns a
**cell**number that matches our criteria. We have set the criteria to the value from**cell C5**, which is**$13,000**. - Then, we define our
*lookup_array*as the**cell**range**B13:B16**. - Finally, we set the match type
**less than**by typing**1**. Thus, we have got the output.

- Then our formula reduces to ->
**INDEX($D$13:$D$16,2)*C5****Output:****1300**.- This function returns a value from a range. It will return the second value from the
**cell**range**D13:D16**, which is**0.1**. - Finally, it will
**multiply**it by the sales value to find the**sliding commission**.

- Finally, press
**CTRL+ENTER**.

This will **AutoFill the formula** to the rest of the **cells**.

Therefore, we use an **Excel **formula to **calculate **the **sliding scale commission**.

### 5. Combining IF & AND Functions to Calculate Sliding Scale Commission

For the last method, we will use **IF **& **AND** functions to find the **sliding scale commission **in **Excel**. Again, we will get the **commission **value on the **whole amount**.

**Steps:**

- First, type the following formula in
**cell D5**.

`=IF(AND(C5>$B$13,C5<$C$13),$D$13,IF(AND(C5>=$B$14,C5<=$C$14),$D$14,IF(AND(C5>=$B$15,C5<=$C$15),$D$15,$D$16)))*C5`

**Formula Breakdown**

- We’re using
**AND**functions inside**IF**functions in this formula. - First, the formula checks where the sales value lies on the
**sliding scale**table. - So, the formula loops through the entire range until it finds the
**appropriate range**. - Next, we
**multiply**the value by the**sales**figure. - Thus, we get the
**sliding commission**value in**Excel**.

- Then, press
**ENTER**, and use the**Fill Handle**to**AutoFill**the formula.

In conclusion, we have shown you all the **5** formulas to **calculate **the **sliding scale commission **in **Excel**.

## Practice Section

We have added a practice dataset for each method in the **Excel** file. Therefore, you can follow along with our methods easily.

## Conclusion

We have shown you **5** quick methods in **Excel formula** to **calculate sliding scale commission**. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site **ExcelDemy** for more **Excel-related** articles. Thanks for reading, keep excelling!

I am having a difficult time coming up with the correct calculation and selecting the correct model for a cumulative sliding commission scale. Can you please point me in the right direction. I thought I knew Excel until I ran into this conundrum and realized there is a wealth of capability in Excel for which I have no knowledge. I could merely calculate the individual commission for each transaction but I feel that would be a missed opportunity. Your help is greatly appreciated.

Broker Commission Schedule

Deal Size Range (Low) Deal Size Range (High) “$ Incremental

Commission” “% Incremental

Commission” “Cumulative

Commission” Rate delta “Effective

Commission”

$0 $5,000,000 $250,000 5.00% $250,000 0.00% 5.00%

$5,000,001 $10,000,000 $150,000 3.00% $400,000 2.00% 4.00%

$10,000,001 $15,000,000 $75,000 1.50% $475,000 1.50% 3.17%

$15,000,001 $30,000,000 $150,000 1.00% $625,000 0.50% 2.08%

$30,000,001 $45,000,000 $112,500 0.75% $737,500 0.25% 1.64%

$45,000,001 $60,000,000 $75,000 0.50% $812,500 0.25% 1.35%

$60,000,001 $100,000,000 $200,000 0.50% $1,012,500 0.00% 1.01%

$100,000,001 $150,000,000 $250,000 0.50% $1,262,500 0.00% 0.84%

$150,000,001 $200,000,000 $250,000 0.50% $1,512,500 0.00% 0.88%

$200,000,001 $250,000,000 $250,000 0.50% $1,762,500 0.00% 0.81%

$250,000,001 $300,000,000 $250,000 0.50% $2,012,500 0.00% 0.75%

$300,000,001 $350,000,000 $250,000 0.50% $2,262,500 0.00% 0.72%

$350,000,001 $400,000,000 $250,000 0.50% $2,512,500 0.00% 0.69%

$400,000,001 $450,000,000 $250,000 0.50% $2,762,500 0.00% 0.67%

$450,000,001 $500,000,000 $250,000 0.50% $3,012,500 0.00% 0.65%

$500,000,001 $550,000,000 $190,000 0.38% $3,202,500 0.12% 0.63%

$550,000,001 $600,000,000 $190,000 0.38% $3,392,500 0.00% 0.61%

$600,000,001 $650,000,000 $190,000 0.38% $3,582,500 0.00% 0.59%

$650,000,001 $700,000,000 $190,000 0.38% $3,772,500 0.00% 0.57%

$700,000,001 $750,000,000 $190,000 0.38% $3,962,500 0.00% 0.56%

$750,000,001 $800,000,000 $125,000 0.25% $4,087,500 0.13% 0.54%

$800,000,001 $850,000,000 $125,000 0.25% $4,212,500 0.00% 0.52%

$850,000,001 $900,000,000 $125,000 0.25% $4,337,500 0.00% 0.51%

$900,000,001 $950,000,000 $125,000 0.25% $4,462,500 0.00% 0.49%

Thank you, JOHN SCURAS, for your comment.

You can use the below formula to determine your commission of the model.

=IF(H36>C33,SUMPRODUCT(–(H36>$C$33:$C$56),(H36-$C$33:$C$56),$D$33:$D$56)+C33*E33,C33*E33)

Here, H36 is the sales amount, C33 is your first Deal Size Range (High), C33:C56 is all values for Deal Size Range (High) column, D33:D56 is all values from the % incremental commission column, and E33 is the first value of the incremental commission (5%).

Best Regards,

Bishwajit

Team ExcelDemy