Formula for tiered commission on different quantity/price levels

Mak1

New Member
Joined
Mar 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Hi hope this makes sense.....

I need to work out commission on (1) quantity of product and (2) its sale price.

I would appreciate some suggestions and help on the 2 points below:

  • The commission is 50% for any sales of products equal to or above £5 value and decreases by 0.1% for every penny less than £5.
    The formula I have for this so far :
    =((0.5-(5-C19)*0.1)*C19) where C19 is the sales price.

    Can this be simplified or tidied up? Also how do I include it allow a sales price over £5 to stick at 50% commission?

  • The above formula is for the first 5000 units. Thereafter every 1000 units sold the commission amount (result of formula from point above) is decreased/discounted by 1%.

    What would the formula be, if the number of units sold is E19 and I wanted to show the total commission in F19?

    For e.g.
    A) If 7000 units were sold at £6.00, commission would be as follows:
    1) 50% for the first 5000 units ( £3 x 5000 ) = £3000
    2) (50% for the units 5001 to 6000 ; £3 x 1000) with a 1% discount = £2970
    3) (50% for the units 6001 to 7000 ; £3 x 1000) with a 2% discount = £2940

    TOTAL £8910

    B) If 7000 units were sold at £0.60, commission would be as follows:
    1) 6% for the first 5000 units ( £0.036 x 5000 ) = £180
    2) (6% for the units 5001 to 6000 ; £0.036 x 1000) with a 1% discount = £35.64
    3) (6% for the units 6001 to 7000 ; £0.036 x 1000) with a 2% discount = £35.28

    TOTAL £250.92


    Should you have any questions please let me know.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,236
Office Version
  1. 365
Platform
  1. Windows
Here is one approach...inputs in blue, calculations in green cells:
MrExcel20220319.xlsx
BCDE
18Units SoldSales PriceTiersCommission
197001£6.005000£15,000.00
201000£2,970.00
211000£2,940.00
221£2.91
23  
24  
Sheet1
Cell Formulas
RangeFormula
D19D19=MIN(5000,$B$19)
E19E19=((0.5-MAX(0,0.001*100*(5-C19)))*C19)*D19
D20:D24D20=IF(SUM(D$19:D19)=$B$19,"",MIN(1000,$B$19-SUM(D$19:D19)))
E20:E24E20=IF(ISNUMBER(D20),$E$19/$D$19*D20*(1-ROWS(E$19:E19)/100),"")

Could you confirm your answer in Example A1? (3*5000)
 
Upvote 0

Mak1

New Member
Joined
Mar 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Thank you Kirk.
First of all yes answer in example A1 should be £15,000.

I've managed to work it out with a differential rate column in this formula below:

Commission-Plan-gbp.xlsx
ABCDEFGH
2
3Tier MinTier MaxPayoutDif RateSales Price£6.00 per kg
4-5,000£ 3.003.0000000000Amount7001kg
515,0016,000£ 2.970(0.0300000000)Payout£ 20,913.00
626,0017,000£ 2.940(0.0300000000)
737,0018,000£ 2.910(0.0300000000)
848,0019,000£ 2.880(0.0300000000)
959,00110,000£ 2.850(0.0300000000)
Rate Table
Cell Formulas
RangeFormula
D4D4=IF($H$3>5,$H$3*0.5,((0.5-(5-$H$3)*0.1)*$H$3))
E4E4=D4
D5:D9D5=$D$4*((100-A5)*0.01)
E5:E9E5=D5-D4
H5H5=SUMPRODUCT(--(H4>$B$4:$B$29),--(H4-$B$4:$B$29),$E$4:$E$29)



So my next problem thereafter is bringing this forward onto another sheet which will have various lines of products with different sales prices and amounts. I've managed to bring the results forward as below but am stuck on how to apply the sales price variable within this:

Commission-Plan-SUMPRODUCT-Units-gbp.xlsx
ABCDE
1trial sheet
2
3ProductAmount soldSales priceCommission
4
517001£ 6.0020913.00
627001£ 0.6020913.00
73108£ 10.80324.00
84432£ 8.641296.00
95864£ 4.322592.00
1061080£ 1.803240.00
Sheet1
Cell Formulas
RangeFormula
E5:E10E5=SUMPRODUCT(--(C5>'Rate Table'!$B$4:$B$29),--(C5-'Rate Table'!$B$4:$B$29),'Rate Table'!$E$4:$E$29)


Your help is much appreciated
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can try or review these versions.
You could also name the ranges or the arrays. I named the bracket array and rate differentials array as follows:
Brackets are named aB = {0;5000;6000;7000;8000;9000}
Rate differences are named aR ={3;-0.030;-0.0297;-0.0294;-0.0291;-0.0288}
N.B. F1 is an example with Excel 365.

Commission2022.xlsm
ABCDEF
1Amount7,001.0020,913.2120,913.2120,913.21
2
3BracketsPayout
4
503.00
65,0002.97
76,0002.94
87,0002.91
98,0002.88
109,0002.85
1f
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT(--(C1>B5:B10),C1-B5:B10,C5:C10-C4:C9)
E1E1=SUMPRODUCT(--(C1>aB),C1-aB,aR)
F1F1=SUM(IF(C1>aB,(C1-aB)*aR))
C6:C10C6=C5*0.99
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Commission2022.xlsm
ABCDE
1Amount7,000.00250.92250.92
2
3BracketsPayout
4
506.00%
65,0005.94%
76,0005.88%
87,0005.82%
98,0005.76%
109,0005.71%
1ff
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT(--(C1>B5:B10),C1-B5:B10,C5:C10-C4:C9)*0.6
E1E1=SUM(IF(C1>B5:B10,(C1-B5:B10)*(C5:C10-C4:C9)))*0.6
C6:C10C6=0.99*C5
 
Upvote 0

Mak1

New Member
Joined
Mar 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
You can try or review these versions.
You could also name the ranges or the arrays. I named the bracket array and rate differentials array as follows:
Brackets are named aB = {0;5000;6000;7000;8000;9000}
Rate differences are named aR ={3;-0.030;-0.0297;-0.0294;-0.0291;-0.0288}
N.B. F1 is an example with Excel 365.

Commission2022.xlsm
ABCDEF
1Amount7,001.0020,913.2120,913.2120,913.21
2
3BracketsPayout
4
503.00
65,0002.97
76,0002.94
87,0002.91
98,0002.88
109,0002.85
1f
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT(--(C1>B5:B10),C1-B5:B10,C5:C10-C4:C9)
E1E1=SUMPRODUCT(--(C1>aB),C1-aB,aR)
F1F1=SUM(IF(C1>aB,(C1-aB)*aR))
C6:C10C6=C5*0.99


Thank you Dave.

I now have difficulty taking this forward onto to another sheet (Sheet2) where I want to list different products sold (with different prices and amounts) that stick to this commission scale.

Line brings forward the sales price of £6.00 and the answer in E5 is correct, but as you can see the answers in E6:E10 is where i'm now stuck and need some pointers please:

Book2
ABCDE
1trial sheet
2
3ProductAmount soldSales priceCommission
4
517001£ 6.0020913.21
627001£ 0.6020913.21
73108£ 10.80324.00
84432£ 8.641296.00
95864£ 4.322592.00
1061080£ 1.803240.00
Sheet2
Cell Formulas
RangeFormula
E5:E10E5=SUMPRODUCT(--(C5>'1f'!$B$5:$B$10),C5-'1f'!$B$5:$B$10,'1f'!$C$5:$C$10-'1f'!$C$4:$C$9)



Sheet 1f below for ease of reference:

Book2
ABCD
1Amount700120913.21
2
3BracketsPayout
4
503
650002.97
760002.9403
870002.910897
980002.881788
1090002.85297
1f
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT(--(C1>B5:B10),C1-B5:B10,C5:C10-C4:C9)
C6:C10C6=C5*0.99



p.s. thank you for the tip about naming the arrays; I will look into simplifying it further once I find a way to populate Sheet2 E6:E10 correctly
 
Upvote 0

Mak1

New Member
Joined
Mar 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Commission2022.xlsm
ABCDE
1Amount7,000.00250.92250.92
2
3BracketsPayout
4
506.00%
65,0005.94%
76,0005.88%
87,0005.82%
98,0005.76%
109,0005.71%
1ff
Cell Formulas
RangeFormula
D1D1=SUMPRODUCT(--(C1>B5:B10),C1-B5:B10,C5:C10-C4:C9)*0.6
E1E1=SUM(IF(C1>B5:B10,(C1-B5:B10)*(C5:C10-C4:C9)))*0.6
C6:C10C6=0.99*C5

I've just noticed this.... and this may help with the question I just asked in my reply to your first answer..... looking into it now and will let you know if this helps :)
 
Upvote 0

Mak1

New Member
Joined
Mar 18, 2022
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I've just noticed this.... and this may help with the question I just asked in my reply to your first answer..... looking into it now and will let you know if this helps :)
Realised this is for example 2.

So, I'm still stuck on how to bring this forward on Sheet 2 E6:E10 explained my previous reply
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What are the brackets and rates if you base the calculations on the value of the of the sales?

With that information, what are your expected results?
 
Upvote 0

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,664
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your approach may be to derive a factor based on volume and diminishing rate to apply to the selling price.
Each of the formulas shown earlier can do the following.

Commission2022.xlsm
ABCDE
1
2
3BracketsPayout
4
5050.00%
65,00049.50%
76,00049.01%
87,00048.51%
98,00048.03%
109,00047.55%
11
12NumberPriceAmount
137,0016.0020,913.21
1410810.80583.20
154328.641,866.24
16
1ff
Cell Formulas
RangeFormula
E6:E10E6=E5*0.99
D13:D15D13=SUM(IF(B13>aB,(B13-aB)*aR))*C13
 
Upvote 0

Forum statistics

Threads
1,186,639
Messages
5,958,928
Members
438,384
Latest member
RedJeep1970

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top