Commission over 3month hurdle

[madmansgates]

Hi,
Is there a way of getting excel to work out monthly commission on the following basis:

Commission rates:
0 - 20,000 - 10%
20,001 - 40,000 - 15%
40,001 - 60,000 - 20%
60,001 - 25%

In any 3 months commission is paid monthly depending on sales per month over 3 months commision hurdle.
eg: Month 1 Sales of 15k is 15k@10%=1500 commission
Month 2 sales of 20k will have commision of 5k@10%(to reach 20k hurdle) and 15k@15% =total of 2750,
Month 3 sales of 10k will have commission of  5k @15% (40k hurdle) & 5k at 20% total of 1750

Any help would be appreciated
Thanks

 

[rallcorn]

This is indeed possible - in past I had used the same for projecting employer payroll taxes based on when pay thresholds were reached. The formulas are too long to display in post, so I have a sample spreadsheet. If you'll send me a private message with your e-mail address, I'll send you the file.

 

[mgirvin]

I am posting because I have a formula that I am not happy with. It is all that I could think up, but I am hoping someone else will see it and say, I can do better than that!

Here is the data and formula results (in green):

Excel Workbook
	A	B	C	D	E	F	G	H
1		hurdle	Rate (used in formula)
2		0	10.0%
3		20,000	15.0%
4		40,000	20.0%
5		60,000	25.0%
6
7		Jan	Feb	Mar	Apr	May	Jun	Jul
8	Sales (INPUT AMTS)	15,000.00	20,000.00	10,000.00	18,000.00	10,000.00
9	Formula 1	1,500.00	2,750.00	1,750.00	3,750.00	2,500.00	0.00	0.00
Commission

Excel 2010


Here is the formula that I put into B9 and copied to the side:

=IF(VLOOKUP(SUM($B8:B8),$B$2:$B$5,1)-SUM(A8:$B8)<0,B8*VLOOKUP(SUM($B8:B8),$B$2:$C$5,2),(VLOOKUP(SUM($B8:B8),$B$2:$B$5,1)-SUM(A8:$B8))*INDEX($C$2:$C$5,MATCH(SUM($B8:B8),$B$2:$B$5)-1)+(SUM($B8:B8)-VLOOKUP(SUM($B8:B8),$B$2:$B$5,1))*INDEX($C$2:$C$5,MATCH(SUM($B8:B8),$B$2:$B$5)))

or

Excel Workbook
	B
9	1,500.00
Commission

Excel 2010

Cell Formulas
Range	Formula
B9	=IF(VLOOKUP(SUM($B8:B8),$B$2:$B$5,1)-SUM(A8:$B8)<0,B8*VLOOKUP(SUM($B8:B8),$B$2:$C$5,2),(VLOOKUP(SUM($B8:B8),$B$2:$B$5,1)-SUM(A8:$B8))*INDEX($C$2:$C$5,MATCH(SUM($B8:B8),$B$2:$B$5)-1)+(SUM($B8:B8)-VLOOKUP(SUM($B8:B8),$B$2:$B$5,1))*INDEX($C$2:$C$5,MATCH(SUM($B8:B8),$B$2:$B$5)))

Any ideas?

 

[Dave Patton]

try the following


=SUMPRODUCT((SUM($B$9:B9)>$B$3:$B$6)*(SUM($B$9:B9)-$B$3:$B$6)*($C$3:$C$6-$C$2:$C$5))-SUM($A$12:A12)

or naming the Brackets "B" and the range of rates "Rate"

=SUMPRODUCT((SUM($B$9:B9)>B)*(SUM($B$9:B9)-B)*(Rate-OFFSET(Rate,-1,0)))-SUM($A$12:A12)

 

[mgirvin]

Thanks Dave!

I can't get it to work. Can you show me how your data is set up?

 

[Dave Patton]

My formula considers the example in post 3.

Make sure you don't have merged cells and ensure cells C2 and A12 are empty or have 0.

 

[mgirvin]

Dave,

Why does C2 have to be zero? It should have 10% in it. Also, why cell A12? That cell is below any of the data or formulas?

 

[Dave Patton]

Please review the ranges that I used in the formula. You can insert a new row 2 or edit the formula.

The first thing that I did was remove all merged cells. I didn't delete a row; consequently, the data ranges for my formula are a row lower than your data.

You can edit the range of the brackets/hurdles A3:A6 and the rates C3:C6.

I put the formula in B12; A12 must be blank or contain 0.

You can review the formula with Excel's Evaluate Formula.

Dave

 

[West Man]

Mike, assuming the sales amout is in cell C10, try: =(C10*0.1)+MAX(0,(C10-20000)*0.05)+MAX(0,(C10-40000)*0.05)+MAX(0,(C10-60000)*0.05)

 

[mgirvin]

Dear Dave,

Thanks! It works.

It was helpful to know that you had inserted a row and that you had placed your formula in cell B12. I kept trying to figure out how to relate your formula to the 'example in post 3'. I couldn't for the life of me figure why the formula was not including all the bracket values!

Your formula is quite elegant! And the concept is straight forward: calculate all the amounts based on differences and then subtract the cumulative amount from the previous commission amounts!

Thanks again!