Tiered Commission Structure

[Mi_KaSa]

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:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]# of Members[/TD]
[TD]Commission Per Member[/TD]
[/TR]
[TR]
[TD]0-10[/TD]
[TD]$3.50[/TD]
[/TR]
[TR]
[TD]11-30[/TD]
[TD]$2.00[/TD]
[/TR]
[TR]
[TD]31-100[/TD]
[TD]$1.00[/TD]
[/TR]
[TR]
[TD]>100[/TD]
[TD].50[/TD]
[/TR]
</tbody>[/TABLE]









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!

 

[Dave Patton]

Re: Tiered Commission Structure Help

Excel 2010
	C	D	E	F	G	H
1				# of Members	Commission Per Member	Difference
2	120	155		0	3.50	3.50
3		155		10	2.00	-1.50
4				30	1.00	-1.00
5				100	0.50	-0.50
6
4c

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
rB	='4c'!$F$2:$F$5
rD	='4c'!$H$2:$H$5

 

[Kreg007]

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?

 

[Mi_KaSa]

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

	C	D	E	F	G	H
Difference

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]# of Members[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Commission Per Member[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3.50[/TD]
[TD="align: right"]3.50[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2.00[/TD]
[TD="align: right"]-1.50[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.00[/TD]
[TD="align: right"]-1.00[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0.50[/TD]
[TD="align: right"]-0.50[/TD]

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

</tbody>

4c​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=G2-N(G1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=SUMPRODUCT(--(C2>rB),C2-rB,rD)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=(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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]rB[/TH]
[TD="align: left"]='4c'!$F$2:$F$5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]rD[/TH]
[TD="align: left"]='4c'!$H$2:$H$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mi_KaSa]

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...

 

[Dave Patton]

Re: Tiered Commission Structure Help

The Sumproduct without the Table follows

Excel 2010
	D
4	155
4c

Cell Formulas
Range	Formula
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