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

 

[awoohaw]

what is a slab?

Please post a sample of your data, and expected results?
What version of excel do you use? Please update your profile so the forum can give you an answer that fits your excel version.

 

[amandeep08]

what is a slab?

Please post a sample of your data, and expected results?
What version of excel do you use? Please update your profile so the forum can give you an answer that fits your excel version.

Hi,
I am using Office 365. I have uploaded the data with example. Pl help if possible.

Book1
	A	B	C	D	E	F	G	H	I	J
1			Required Data				Slab Master
2	Customer Name	Sales	Commission (%)				Customer Name	Start Slab	End Slab	Margin
3	A	1,00,00,000	8%				A	-	30,00,000	5%
4	B	2,00,00,000	7%				A	30,00,001	60,00,000	6%
5	C	3,00,00,000	1%				A	60,00,001	90,00,000	7%
6							A	90,00,001		8%
7							B	-	70,00,000	3%
8							B	70,00,001	1,00,00,000	5%
9							B	1,00,00,001	3,00,00,000	7%
10							B	3,00,00,001		9%
11							C	-	5,00,00,000	1%
12							C	5,00,00,001	10,00,00,000	2%
13							C	10,00,00,001	20,00,00,000	3%
14							C	20,00,00,001		4%
Customer Data

 

[awoohaw]

try this:

Book4
	A	B	C	D	E	F	G	H	I	J
1			Required Data				Slab Master
2	Customer Name	Sales	Commission (%)				Customer Name	Start Slab	End Slab	Margin
3	A	10,000,000	0.08	0.08			A	0	3,000,000	0.05
4	B	20,000,000	0.07	0.07			A	3,000,001	6,000,000	0.06
5	C	30,000,000	0.01	0.01			A	6,000,001	9,000,000	0.07
6							A	9,000,001		0.08
7							B	0	7,000,000	0.03
8							B	7,000,001	10,000,000	0.05
9							B	10,000,001	30,000,000	0.07
10							B	30,000,001		0.09
11							C	0	50,000,000	0.01
12							C	50,000,001	100,000,000	0.02
13							C	100,000,001	200,000,000	0.03
14							C	200,000,001		0.04
Sheet3

Cell Formulas
Range	Formula
D3:D5	D3	=LET(CN,A3,CS,B3,Cnames,$G$3:$G$14,Start,$H$3:$H$14,End,IF($I$3:$I$14="",2^1000,$I$3:$I$14),Margin,$J$3:$J$14, FILTER(Margin,(CN=Cnames)*(CS>=Start)*(CS<=End),""))

 

[amandeep08]

try this:

Thanks. This is working fine. I also have slabs which are prospective in nature. Can you help here as well:

what is a slab?

Please post a sample of your data, and expected results?
What version of excel do you use? Please update your profile so the forum can give you an answer that fits your excel version.

Thanks. This is working fine. I also have slabs which are prospective in nature. Can you help here as well:

 

[amandeep08]

Book2
	A	B	C	D	E	F	G	H	I
1			Prospective Slab Required
2	Customer Name	Sales	Amount			Customer Name	Start Slab	End Slab	Margin
3	A	1,00,000	3,400			A	-	10,000	2%
4	B	2,00,000	16,400			A	10,001	50,000	3%
5						A	50,001	1,00,000	4%
6						A	1,00,001		5%
7						B	-	10,000	6%
8						B	10,001	50,000	7%
9						B	50,001	1,00,000	8%
10						B	1,00,001		9%
Sheet1

Cell Formulas
Range	Formula
C3	C3	=((H3-G3)*I3)+((H4-G4+1)*I4)+((H5-G5+1)*I5)
C4	C4	=((H7-G7)*I7)+((H8-G8+1)*I8)+((H9-G9+1)*I9)+((B4-G10+1)*I10)

 

[Dave Patton]

Do you you require the blended rate or the commission amount?

 

[amandeep08]

Do you you require the blended rate or the commission amount?

Commission amount should come up basis the customer slabs

 

[Dave Patton]

​

You can build your own factor for each set of common brackets and rates.
I will post the information for the calculation relevant for B.
The function prompts for the required input which is the sales amount.
There are many examples in the forum of tiered calculations.

Commissions 2024.xlsm
	A	B	C	D
1
2	Customer Name	Sales	Commission	Commission
3
4	B	200,000.00	16,400.00	16,400.00
5
1f

Cell Formulas
Range	Formula
C4	C4	=((H7-G7)*I7)+((H8-G8+1)*I8)+((H9-G9+1)*I9)+((B4-G10+1)*I10)
D4	D4	=B_Commission(B4)

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

 

[amandeep08]

​

You can build your own factor for each set of common brackets and rates.
I will post the information for the calculation relevant for B.
The function prompts for the required input which is the sales amount.
There are many examples in the forum of tiered calculations.

Commissions 2024.xlsm
	A	B	C	D
1
2	Customer Name	Sales	Commission	Commission
3
4	B	200,000.00	16,400.00	16,400.00
5
1f

Cell Formulas
Range	Formula
C4	C4	=((H7-G7)*I7)+((H8-G8+1)*I8)+((H9-G9+1)*I9)+((B4-G10+1)*I10)
D4	D4	=B_Commission(B4)

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

This won't work because if the starting slab and end slab is different from customer to customer, then this formula will not give right calculation