Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


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.


How to Calculate Sliding Scale Commission with Excel Formula: 5 Suitable Examples

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.

excel formula to calculate sliding scale commission Intro


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

excel formula to calculate sliding scale commission Calculator Maker

  • 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

excel formula to calculate sliding scale commission Calculator 2

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.

excel formula to calculate sliding scale commission Calculator 3

  • 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.

excel formula to calculate sliding scale commission Final Method 1


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 the approximate match will be used by default.
  •  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 Auto-fill the formula to the rest of the cells.

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

excel formula to calculate sliding scale commission


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

excel formula to calculate sliding scale commission SUMPRODUCT

  • 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.
  • 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.

excel formula to calculate sliding scale commission SUMPRODUCT 2


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.

excel formula to calculate sliding scale commission INDEX MATCH


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.

excel formula to calculate sliding scale commission IF Function

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.

excel formula to calculate sliding scale commission Practice Sheet


Download Practice Workbook


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. Thanks for reading, keep excelling!


<< Go Back to Commission Bonus | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. 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%

    • Reply Bishawajit Chakraborty
      Bishawajit Chakraborty Dec 21, 2022 at 4:34 PM

      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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo