Cumulative Tier Commission Formula on excel

[juliojohk]

Good Evening,
Was hoping someone could please help me.

I am trying to create a formula to calculate the commission as below

<80,000 = 0%
80,001-130,000 = 30%
130,001 - 295,000 = 35%
295,001 - 495,000 - 40%
> 495,001 = 50%

so based on person making 500,000 total for the quarter, his bonus will be per below
14,999.7+57,749.65+79,999.6 + 2,499.5
total = 155,248.45

can you please help me on what formula to use to get this calculation?

 

[DRSteele]

Welcome to the forum.

I have an answer for you.

 

[DRSteele]

I think this ought to do it.

	A	B	C
Total Bonus:

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]Quarterly [/TD]
[TD="bgcolor: #FCE4D6, align: right"]500000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFF2CC"]Hurdle[/TD]
[TD="bgcolor: #FFF2CC"]Rate[/TD]
[TD="bgcolor: #FFF2CC"]Bonus[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"]0%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] - [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] 80,001 [/TD]
[TD="align: right"]30%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 14,999.70 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"] 130,001 [/TD]
[TD="align: right"]35%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 57,749.65 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"] 295,001 [/TD]
[TD="align: right"]40%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 79,999.60 [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"] 495,001 [/TD]
[TD="align: right"]50%[/TD]
[TD="bgcolor: #C6E0B4, align: right"] 2,499.50 [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"] 155,248.45 [/TD]

</tbody>

Sheet35​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=IF($B$1 < A4,0,IF($B$1 > A5,A5-A4-1,$B$1-A4)*B4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=IF($B$1 < A5,0,IF($B$1> A6,A6-A5-1,$B$1-A5)*B5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=IF($B$1 < A6,0,IF($B$1> A7,A7-A6-1,$B$1-A6)*B6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=IF($B$1 < A7,0,IF($B$1> A8,A8-A7-1,$B$1-A7)*B7)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=IF($B$1 < A8,0,($B$1-A8)*B8)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=SUM(C4:C8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dave Patton]

Excel 2010
	B	C	D	E
1	500,000.00	155,250.00
2
3		80,000.01	30%	30%
4		130,000.01	35%	5%
5		295,000.01	40%	5%
6		495,000.01	50%	10%
2c

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(B1>rB),B1-rB,rDiff)
E3	=D3-G2

Named Ranges
Name	Refers To	Cells
rB	='2c'!$C$3:$C$6
rDiff	='2c'!$E$3:$E$6

Please review the definition of the Brackets Tiers and edit the range if necessary.

 

[Dave Patton]

=(b1>80000)*(b1-80000)*e3+(b1>130000)*(b1-130000)*e4+(b1>295000)*(b1-295000)*e5+(b1>495000)*(b1-495000)*e6

 

[juliojohk]

=(b1>80000)*(b1-80000)*e3+(b1>130000)*(b1-130000)*e4+(b1>295000)*(b1-295000)*e5+(b1>495000)*(b1-495000)*e6

Thankyou!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[juliojohk]

Thank you so much for your help!

 

[pike]

Hi,
I calculate $155,250.00 with

=SUMPRODUCT((A1>{80000,130000,295000,495000})*(A1-{80000,130000,295000,495000})*{0.3,0.05,0.05,0.1})