Figuring out cumulative commissions

[Markkram]

I'm trying to figure out how to calculate commissions paid to employees based on their sales and where the commission rate varies (the more they sell, the more they make in commissions). I think this problem is much like trying to figure out marginal tax rates. Anyway, the commission rate is shown below.

Units Sold Unit Price Commission Rate
1 through 499 $35.64 1.00%
500 through 999 $35.64 2.00%
1000 through 1,999 $35.64 3.00%
2,000 through 4,999 $35.64 4.00%
5,000 and more $35.64 5.00%

So a person who sold 1,200 units would earn $749.15 in commissions. That $749.15 number is calculated as follows:

* First 499 units sold at $35.64 per unit: Earns $177.84 (1% commission rate); plus

* Then the next 500 units sold at $35.64 per unit: Earns $356.80 (2% commission rate); plus

* Last 201 units sold at $35.64 per unit: Earns $214.91 (3% commission rate).

[I have attached a spreadsheet that reflects the commission rates described above]

I have to calculate the commissions on lots of employees so I thought a vlookup would be neatest. But, alas my attempts to figure out how to make this work have not worked.

Any help here (whether its vlookup or something else) are much appreciated.

Thank you for your time.

 

[bizlife]

Hi,

I have used if statement along with and operator. The formula can be further simplified using named ranges and by restructuring your table.
Refer the attached file.
Best Regards,
Pankaj

 

[pecoflyer]

This link will provide a flexible answer http://www.mcgimpsey.com/excel/variablerate1.html

 

[Markkram]

Pankaj: Thank you for providing me with a workable solution to my problem.
Pecoflyer: Thank you for providing me with a resource to increase my Excel knowledge base.

 

[John Jairo V]

Hi, to all!

Another option in the attached file. Blessings!

 

[Markkram]

John: A very nice option indeed. I didn't think such a short formula would work in this instance ... but it does. I can't claim to understand how your formula works - but it does - which means I need to study up on array formulas. Thanks again.

 

[bosco_yip]

Another option,



1] Create a "Min Tier" at column B as shown in above picture.

2] In "Sold Amount" E2, enter criteria of 1,200 or other amount.

3] In "Earn breakdown" E3, formula copy down to E7 :

=IF(E$2<B3,0,IF(E$2>B4,B4-B3,E$2-B3)*C3*D3)

4] In "Total earn" E8, formula :

=SUM(E3:E7)

Regards
Bosco

 

[John Jairo V]

Hi, to all!

If you use helper column (like @bosco_yip - greetings to you -), The formula can be shortened (In E3 of bosco's file) to:

=MAX(,MIN(B4,E$2)-B3)*C3*D3

And copy it down until E7. Blessings!

 

[bosco_yip]

The non-array formula without helper way,



In B9, copy down :

=SUMPRODUCT(--(A9>A$2:A$6),A9-A$2:A$6,C$2:C$6-N(+C$1:C$5),B$2:B$6)

Regards
Bosco

 

[bosco_yip]

Hi, to all!

If you use helper column (like @bosco_yip - greetings to you -), The formula can be shortened (In E3 of bosco's file) to:

=MAX(,MIN(B4,E$2)-B3)*C3*D3

And copy it down until E7. Blessings!

Hi John,

Max+Min formula is a nice and shorter option,

Although IF+IF formula look longer than Max+Min formula,

I think IF+IF formula will work faster than Max+Min formula.

Regards
Bosco

 

[Markkram]

I appreciate everyone sharing their expertise with regard to the question I posed above. I do like the table that was created and commented upon.

If I may, I have a follow-up question about how to best use that table to fill in the commissions for a lot of employees. That is, in your view, what is the best way to best link the two tables together so that the "total earn" figure from the top table is automatically populated in the 3rd column of the Employee Table for all the employees? I have attached a file that better describes my request.

 

[bosco_yip]

I appreciate everyone sharing their expertise with regard to the question I posed above. I do like the table that was created and commented upon.

If I may, I have a follow-up question about how to best use that table to fill in the commissions for a lot of employees. That is, in your view, what is the best way to best link the two tables together so that the "total earn" figure from the top table is automatically populated in the 3rd column of the Employee Table for all the employees? I have attached a file that better describes my request.

View attachment 43021

See revised file

Regards
Bosco

 

[Markkram]

Thank you again Bosco.