Sales Commission Calculation (Tiered basis)

[AVIJITDE]

Hi All,

looking for a solution in Excel for the below mentioned scenario:

sales person are given sales commissions (tiered basis) on their sale as indicated below:-
- till 600 no commission
- between 601 and 1100, commission at @ $2.00 per piece
- between 1101 and 1500, commission @ $3.00 per piece
- above 1501, commission to be paid @ $4.00 per piece

for e.g.

if a sales person sold 1600 pcs in a month, his commission will be as follows:

for first 600 pcs - 0.00

for 601 – 1100 pcs @ 500 pcs @ 2 i.e. 500*2=1000

for 1101 – 1500 pcs @ 500 pcs @ 3 i.e. 500*3=1500

for 1501 – 1600 pcs @ 100 pcs @ 4 i.e. 100*4=400

Total Commission Earned= 0+1000+1500+400 = $ 2900

 

[Smallman]

Hi Avijitde

I think you have made an error in the following:

for 1101 – 1500 pcs @ 500 pcs @ 3 i.e. 500*3=1500


the difference between 1100 and 1500 is 400 not 500. Here is an example of a formula which will work for you.

The hard coded version is as follows.

=SUMPRODUCT(--(C20>{600;1100;1500}), (C20-{600;1100;1500}), {2;1;1})

Where C20 contains the number 1600.

While I have put it in a formula as well.

=SUMPRODUCT(--(C20>$B$11:$B$13), (C20-$B$11:$B$13), $D$11:$D$13)

I have attached a workbook to show workings. Let me know if you need me to explain it. I have written a blog post on this subject.

Take care

Smallman

 

[AVIJITDE]

Hi Smallman....Thnx a lot....it was my mistake.....sorry for that......now i've changed the data.....but now the same formula is giving me an output which is not ok.....the value is showing as 3000 instead of 2900...can u pls chk the uploaded excel file?

 

[Smallman]

@AVIJITDE

You need to look at where you point your forumula. Nothing wrong with the formula. You just hooked it up to the wrong table. What table is the cell with 3000 point to? Either repoint your formula to the correct lower table or put 1600 in Cell B7. Either way it will produce the right result.

Take care

Smallman