Lookup multiple customers and apply their respective slabs

[amandeep08]

Hi All,

I have 100+ customers and each one is having different commission slabs. I want the formula which will match the customer name and apply the respective slabs on the sales done by them.

I have tried multiple formulas but not able to get success. Apart from this, i have tried putting the slabs in a cell without equal sign and tried to ccalculate the same basis the lookup value but it also not worked. Pl help me

 

[Dave Patton]

As stated, You can build your own factor for each set of common brackets and rates.

If you want to retain the slabs, you can review the following formula approaches and pick the alternative that you prefer.

Commissions 2024.xlsm
	A	B	C	D		F	G		I	J
1
2	Customer Name	Sales	Commission	Commission		Customer Name	Start Slab		Margin
3
4	B	200,000.00		16,400.00
5
6
7						B	0		6%	6%
8						B	10000		7%	1%
9						B	50000		8%	1%
10						B	100000		9%	1%
11
12		16,400.00		16,400.00		16,400.00
13
1f

Cell Formulas
Range	Formula
D4	D4	=B_Commission(B4)
J7:J10	J7	=I7-N(I6)
B12	B12	=LET(s, 200000,b,{0;10000;50000;100000},r,{0.06;0.01;0.01;0.01},SUM((s>b)*(s-b)*r))
D12	D12	=LET(s, 200000,b,{0;10000;50000;100000},r,{0.06;0.01;0.01;0.01},SUMPRODUCT(--(s>b),(s-b),r))
F12	F12	=SUMPRODUCT(--(B4>G7:G10),(B4-G7:G10),J7:J10)

Lambda Functions
Name	Formula
B_Commission	=LAMBDA(Sales,LET(s, Sales,b,{0;10000;50000;100000},r,{0.06;0.01;0.01;0.01},SUM((s>b)*(s-b)*r)))

 

[Peter_SSs]

I am using Office 365.

Please add that to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KRice]

I wish I had seen this thread first...but I recently responded to an unanswered thread dealing with the same problem here:

Match customer and calculate commission basis their respective slabs

Hi All, I have more than 100 customers, their sales data and their slabs. I want to Match customer and calculate commission basis their respective slabs. Pl help providing formula. I am using office 365. ABCDEFGH1Required Field2Customer NameSalesProspective CommissionCustomer Name Start Slab...

www.mrexcel.com

...which is now closed. I see that Dave shares my concern about the gap of "1" between the end of one slab and the beginning of another, as the slab start array in his LAMBDA function is the same idea that I mentioned. I think that's a cleaner approach to close the gap between slabs. In any case, to the OP, my suggestion in the other thread offers a way to extract the slab details for each name, and then the computation method is essentially the same as what Dave describes above. My formula computes the same differential commission rates ("r" in post 9 above) and redefined start slab quantities ("b" in post 9), although it might be easier to implement than hardcoding those arrays in this case, because they change for every name. If you have any comments/questions about the other thread, just post here and I'll receive a notification.

 

[Dave Patton]

Please review the formulas and select the format that works best for you; Excel's Formulas Formula Evaluate may help.
I added arithmetic columns that also illustrate the calculations.
You can put the brackets and rate differentials and calculation in one row if that helps.
When you are comfortable with the formulas and the results, you may consider just using the formula like D3.

Commissions 2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Sales	Commission v1	Commission v2	Commission v3		Name	Brackets	Rates	Rate Differential	Arithmetic	Arithmetic
2	A	100,000.00	3,400.00	3,400.00	3,400.00		A	0	0.02	0.02	200.00	2,000.00
3	S	200,000.00	14,750.00	14,750.00	14,750.00		A	10000	0.03	0.01	1,200.00	900.00
4							A	50000	0.04	0.01	2,000.00	500.00
5							A	125000	0.05	0.01		0.00
6							A	1E+308
7							S	0	0.06	0.06	3,000.00	12,000.00
8							S	50000	0.07	0.01	3,500.00	1,500.00
9							S	100000	0.08	0.01	6,000.00	1,000.00
10							S	175000	0.09	0.01	2,250.00	250.00
11								1E+308
12
1ff

Cell Formulas
Range	Formula
C2	C2	=SUM((B2>H2:H5)*(B2-H2:H5)*J2:J5)
D2	D2	=LET(b,{0;10000;50000;125000},rd,{0.02;0.01;0.01;0.01},SUM((B2>b)*(B2-b)*rd))
E2:E3	E2	=SUMIFS($L$2:$L$10,$G$2:$G$10,A2)
C3	C3	=SUM((B3>H7:H10)*(B3-H7:H10)*J7:J10)
D3	D3	=LET(b,{0;50000;100000;175000},rd,{0.06;0.01;0.01;0.01},SUM((B3>b)*(B3-b)*rd))
K2:K4	K2	=MAX(0,MIN($B$2-H2,H3-H2))*I2
J2:J5,J8:J10	J2	=I2-N(I1)
L2:L5	L2	=MAX(0,($B$2-H2)*J2)
H6,H11	H6	=BigNum
J7	J7	=I7
K7:K10	K7	=MAX(0,MIN($B$3-H7,H8-H7))*I7
L7:L10	L7	=MAX(0,($B$3-H7)*J7)

Commissions 2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Sales	Commission v1			Brackets				Rate Differentials
2	A	100,000.00	3,400.00			0.00	10,000.00	50,000.00	125,000.00	0.02	0.01	0.01	0.01
3	S	200,000.00	14,750.00			0.00	50,000.00	100,000.00	175,000.00
4	B	200,000.00	16,400.00			0.00	10,000.00	50,000.00	100,000.00	0.06	0.01	0.01	0.01
5
1fff

Cell Formulas
Range	Formula
C2	C2	=SUM((B2>F2:I2)*(B2-F2:I2)*J2:M2)
C3	C3	=SUM((B3>F3:I3)*(B3-F3:I3)*{0.06,0.01,0.01,0.01})
C4	C4	=LET(s, B4,b,F4:I4,r,J4:M4,SUM((s>b)*(s-b)*r))

 

[Dave Patton]

Commissions 2024.xlsm
	A	B		D
1	Name	Sales		Commission v2
2	A	100,000.00		3,400.00
3	S	200,000.00		14,750.00
4
1ff

Cell Formulas
Range	Formula
D2	D2	=LET(b,{0;10000;50000;125000},rd,{0.02;0.01;0.01;0.01},SUM((B2>b)*(B2-b)*rd))
D3	D3	=LET(b,{0;50000;100000;175000},rd,{0.06;0.01;0.01;0.01},SUM((B3>b)*(B3-b)*rd))

 

[Dave Patton]

I adapted Kirk's suggestion.
1. I cleaned up the source data
2. I calculated the rate differential in the rate information range
3. I named the new Lambda formula in Name Manager; name the formula to a user-friendly name relevant for you.
4. The formula prompts for the rate information range, customer name (reference), and Sales amount (reference)

Commissions 2024.xlsm
	A	B	C	D	E	F	G	H	I
1	Customer Name	Sales	Commission	Commission		Customer Name	Brackets	Rates	Rate Differential
2	A	100,000.00	3,400.00	3,400.00		A	0	2%	2%
3	S	200,000.00	14,750.00	14,750.00		A	10,000	3%	1%
4						A	50,000	4%	1%
5						A	125,000	5%	1%
6						S	0	6%	6%
7						S	50,000	7%	1%
8						S	100,000	8%	1%
9						S	175,000	9%	1%
10
1gg

Cell Formulas
Range	Formula
C2:C3	C2	=LET(rinfo,$F$2:$I$9,cslab,FILTER(rinfo,CHOOSECOLS(rinfo,1)=A2),b,CHOOSECOLS(cslab,2),r,CHOOSECOLS(cslab,4),SUM((B2>b)*(B2-b)*r))
D2:D3	D2	=Commissions_X(F2:I9,A2,B2)

Lambda Functions
Name	Formula
Commissions_X	=LAMBDA(rngRateData,customer,Sales,LET(rinfo,rngRateData,cn,customer,s,Sales,cslab,FILTER(rinfo,CHOOSECOLS(rinfo,1)=cn),b,CHOOSECOLS(cslab,2),r,CHOOSECOLS(cslab,4),SUM((s>b)*(s-b)*r)))

 

[Dave Patton]

Commissions 2024.xlsm
	A	B	C	D	E	F	G	H	I
1	Customer Name	Sales	Commission				Customer Name	Brackets	Rates
2	A	100,000.00	3,400.00				A	0	2%
3	B	200,000.00	16,400.00				A	10,000	3%
4	S	200,000.00	14,750.00				A	50,000	4%
5	S	300,000.00	23,750.00				A	125,000	5%
6	A	300,000.00	13,150.00				B	0	6%
7	B	300,000.00	25,400.00				B	10,000	7%
8							B	50,000	8%
9							B	100,000	9%
10							S	0	6%
11							S	50,000	7%
12							S	100,000	8%
13							S	175,000	9%
14
1g

Cell Formulas
Range	Formula
C2:C7	C2	=Comm_x($G$2:$I$13,A2,B2)

Lambda Functions
Name	Formula
Comm_x	=LAMBDA(rngRateData,customer,Sales,LET(rng,rngRateData,cn,customer,s,Sales,cslab,FILTER(rng,CHOOSECOLS(rng,1)=cn),b,CHOOSECOLS(cslab,2),r,CHOOSECOLS(cslab,3),ro,VSTACK(0,DROP(r,-1)),SUM((s>b)*(s-b)*(r-ro))))

 

[amandeep08]

I wish I had seen this thread first...but I recently responded to an unanswered thread dealing with the same problem here:

Match customer and calculate commission basis their respective slabs

Hi All, I have more than 100 customers, their sales data and their slabs. I want to Match customer and calculate commission basis their respective slabs. Pl help providing formula. I am using office 365. ABCDEFGH1Required Field2Customer NameSalesProspective CommissionCustomer Name Start Slab...

www.mrexcel.com

...which is now closed. I see that Dave shares my concern about the gap of "1" between the end of one slab and the beginning of another, as the slab start array in his LAMBDA function is the same idea that I mentioned. I think that's a cleaner approach to close the gap between slabs. In any case, to the OP, my suggestion in the other thread offers a way to extract the slab details for each name, and then the computation method is essentially the same as what Dave describes above. My formula computes the same differential commission rates ("r" in post 9 above) and redefined start slab quantities ("b" in post 9), although it might be easier to implement than hardcoding those arrays in this case, because they change for every name. If you have any comments/questions about the other thread, just post here and I'll receive a notification.

Sir,

You have given the formula and it is working fine. Just wanted to check if i am inserting one column between G & H, how the formula will be changed. Pl help he as i am not able to understand your formula.

 

[KRice]

In the version I posted, the slab lookup data are defined as variable "slab" located in the range $E$3:$H$10, It sounds as if the range will now be something like slab,$E$3:$G$10. Consider this to be a mini-table, so column E has a column index of 1, column F is 2, column G is 3, and column H is 4. The rest of formulas reference the columns like this.

Because each Customer Name might have different slab tiers and commission rates, we need to extract just the portion of the main "slab" table that applies to that particular customer. So "cslab" does this by applying a FILTER to "slab", and it requires CHOOSECOLS(slab,1)=A3, meaning that the Customer Name in "slab" column 1 (that's actually column E) matches the name in cell A3. In the example, cell A3 is customer A, and the resulting cslab lookup table will be:

A	0​	10000​	0.02​
A	10001​	50000​	0.03​
A	50001​	125000​	0.04​
A	125001​		0.05​

The 3rd column here (original column G labeled "End Slab") is used to create a new array for the "Start Slab" that begins at 0 and adopts all of the existing end slab tier values as the start slab values. This adjustment addresses a problem with the way the original slab tiers are defined. For example, if a lookup value of 10000.5 is given, the original table cannot place that value in any of the tiers because it exceeds the "end slab" value of 10000 of the first tier, but it is below the "start slab" threshold of 10001 for the second tier. What I've just described is performed in this step for variable "csslab"...Customer specific start slab...VSTACK(0,DROP(CHOOSECOLS(cslab,3),-1))
and it converts this End Slab...

10000​

50000​

125000​

to this new Start Slab...

0.00​

10000​

50000​

125000​

So if your End Slab remains in the 3rd column position, no change is necessary.

Similarly, the Commission column in "cslab" is assigned to the variable named "ccom" and is defined as CHOOSECOLS(cslab,4)...the 4th column. If Commission is now in the 5th column of E:I, the "4" should be changed to "5"...otherwise it remains as "4". Can you post a small example of what the new table structure looks like?

 

[amandeep08]

In the version I posted, the slab lookup data are defined as variable "slab" located in the range $E$3:$H$10, It sounds as if the range will now be something like slab,$E$3:$G$10. Consider this to be a mini-table, so column E has a column index of 1, column F is 2, column G is 3, and column H is 4. The rest of formulas reference the columns like this.

Because each Customer Name might have different slab tiers and commission rates, we need to extract just the portion of the main "slab" table that applies to that particular customer. So "cslab" does this by applying a FILTER to "slab", and it requires CHOOSECOLS(slab,1)=A3, meaning that the Customer Name in "slab" column 1 (that's actually column E) matches the name in cell A3. In the example, cell A3 is customer A, and the resulting cslab lookup table will be:

A	0​	10000​	0.02​
A	10001​	50000​	0.03​
A	50001​	125000​	0.04​
A	125001​		0.05​

The 3rd column here (original column G labeled "End Slab") is used to create a new array for the "Start Slab" that begins at 0 and adopts all of the existing end slab tier values as the start slab values. This adjustment addresses a problem with the way the original slab tiers are defined. For example, if a lookup value of 10000.5 is given, the original table cannot place that value in any of the tiers because it exceeds the "end slab" value of 10000 of the first tier, but it is below the "start slab" threshold of 10001 for the second tier. What I've just described is performed in this step for variable "csslab"...Customer specific start slab...VSTACK(0,DROP(CHOOSECOLS(cslab,3),-1))
and it converts this End Slab...

10000​

50000​

125000​

to this new Start Slab...

0.00​

10000​

50000​

125000​

So if your End Slab remains in the 3rd column position, no change is necessary.

Similarly, the Commission column in "cslab" is assigned to the variable named "ccom" and is defined as CHOOSECOLS(cslab,4)...the 4th column. If Commission is now in the 5th column of E:I, the "4" should be changed to "5"...otherwise it remains as "4". Can you post a small example of what the new table structure looks like?

Hi,
I have new column (Marked Yellow) but not able to calculate the commission amount. Pl help

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Name	Customer Category	Sales	Commission				Customer Name	Customer Category	Start Slab	End Slab	% Margin
2	A	Reseller	8,25,000					A	Reseller	-	5,00,000	1.00%
3	B	Corporate	12,25,000					A	Reseller	5,00,000	7,50,000	0.80%
4	C	Employee	7,35,000					A	Reseller	7,50,000	10,00,000	0.70%
5								A	Reseller	10,00,000		0.60%
6								B	Corporate	-	10,00,000	1.00%
7								B	Corporate	10,00,000	12,00,000	0.85%
8								C	Employee	12,00,000		0.75%
9								C	Employee	-	2,00,000	1.50%
10								C	Employee	2,00,000	5,00,000	1.25%
11								C	Employee	5,00,000	10,00,000	1.00%
12								C	Employee	10,00,000		0.75%
Sheet1