# Commission over 3month hurdle

##### New Member
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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### rallcorn

##### Well-known Member
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

##### Well-known Member
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
ABCDEFGH
1hurdleRate (used in formula)
2010.0%
320,00015.0%
440,00020.0%
560,00025.0%
6
7JanFebMarAprMayJunJul
8Sales (INPUT AMTS)15,000.0020,000.0010,000.0018,000.0010,000.00
9Formula 11,500.002,750.001,750.003,750.002,500.000.000.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
91,500.00
Commission
Excel 2010
Cell Formulas
RangeFormula
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

##### Well-known Member
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

##### Well-known Member

Thanks Dave!

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

#### Dave Patton

##### Well-known Member
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

##### Well-known Member

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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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!

1,106,796
Messages
5,513,482
Members
408,953
Latest member
Skiig