Tiered Commission Structure

Mi_KaSa

New Member
Joined
Jan 15, 2019
Messages
3
Hi Community! I need some assistance please. I am trying to create an Excel file that will help me calculate my commissions, but there's an error in the formula I created.

Here's our structure:
# of MembersCommission Per Member
0-10$3.50
11-30$2.00
31-100$1.00
>100.50

<tbody>
</tbody>









The formula I created (that is not calculating correctly) is:
=(MIN(C2,10)*3.5)+(MAX(MIN(30,C2-10),0)*2)+(MAX(MIN(100,C2-30),0)*1)+(MAX(C2-100,0)*0.5)

Will you please advise what the correct formula is for this commission structure to calculate properly?

Thanks in advance!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Re: Tiered Commission Structure Help


Excel 2010
CDEFGH
1# of MembersCommission Per MemberDifference
212015503.503.50
3155102.00-1.50
4301.00-1.00
51000.50-0.50
6
4c
Cell Formulas
RangeFormula
H2=G2-N(G1)
D2=SUMPRODUCT(--(C2>rB),C2-rB,rD)
D3=(C2>0)*MIN(C2,10)*3.5+(C2>10)*MIN(20,C2-10)*2+(C2>30)*MIN(70,C2-30)*1+(C2>100)*(C2-100)*0.5
Named Ranges
NameRefers ToCells
rB='4c'!$F$2:$F$5
rD='4c'!$H$2:$H$5
 
Last edited:
Upvote 0
Re: Tiered Commission Structure Help

Did you consider using VLOOKUP or INDEX and MATCH with an approximate lookup? Is the structure that you fulfill the first range and then move on to the next range?
 
Upvote 0
Re: Tiered Commission Structure Help

Thank you. I'm going to take a look at this and see if I can follow the logic.


Excel 2010
CDEFGH
1# of MembersCommission Per MemberDifference
212015503.503.50
3155102.00-1.50
4301.00-1.00
51000.50-0.50
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
4c

Worksheet Formulas
CellFormula
H2=G2-N(G1)
D2=SUMPRODUCT(--(C2>rB),C2-rB,rD)
D3=(C2>0)*MIN(C2,10)*3.5+(C2>10)*MIN(20,C2-10)*2+(C2>30)*MIN(70,C2-30)*1+(C2>100)*(C2-100)*0.5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
rB='4c'!$F$2:$F$5
rD='4c'!$H$2:$H$5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Re: Tiered Commission Structure Help

Yes, that is the structure. I thought it would be easier to just have a cell where I put the total number of lives and have another cell calculate the tiered commission based on that original cell for reference, but perhaps that was a bit naive...
 
Upvote 0
Re: Tiered Commission Structure Help

The Sumproduct without the Table follows


Excel 2010
D
4155
4c
Cell Formulas
RangeFormula
D4=SUMPRODUCT(--(C2>{0;10;30;100}),C2-{0;10;30;100},{3.5;-1.5;-1;-0.5})


or you could use the formula shown in the previous suggestion's cell D3
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top