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 Sales Commission Formula in Excel (3 Easy Ways)**

### 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**.

### 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!