How can I calculate targeted count cumulatively

[MosMon]

Greetings,

I have sales numbers for each Distributor. we have a new policy that Distributors will get free units from the mother company if they meet the target. But the problem is this free unit should be calculated based on cumulative sales and counted from the maximum target. Thus, seeking the expert help, please.

An example is given below:
Assume that the distributor sales 3999 unit in a month. thus, free unit calculation is

=1050*3=3150 which will be equivalent to 30*3
Rest, 849, which will be under 530 sales quantity. And free unit is 15
Rest, 319, Which will be under 110 sales quantity. And free unit is 3*2
Rest, 99, Which will be under 40 sales quantity. And free unit is 2*1

So Finally total free unit is: 30*3+15*1+3*2+2*1=113

Condition
Sales Quantity	Free Unit
40	1
110	3
530	15
1050	30

ID of D	Distributor Name	Sales Quantity	Total Free Unit	Warehouse
0002532-2	Distributor 1	660		ABWW
0000874-2	Distributor 2	480		CLWW
0000852-2	Distributor 3	330		ABWW
0000856-2	Distributor 4	220		ABWW
0001714-2	Distributor 5	220		CLWW
0002472-2	Distributor 6	220		BGWW
0001856-2	Distributor 7	130		ABWW
0002851-2	Distributor 8	120		RHWW
0000881-2	Distributor 9	110		SDWW
0001054-2	Distributor 10	110		KHWW

For the Sales of 3999
	1050	530	110	40
	First Slab	Second Slab	Third Slab	Forth Slab
Total Sales	3150	530	220	80
Round	3	1	2	2
Free Unit	90	15	6	2
Total Free Unit	113

 

[KRice]

Here is one approach involving setting up some helper cells to the right of your table (can hide them if deisred). You don't really need the last helper column, but you do need the array starting with the Sales Qty (which has been copied over to column G) and the results of the next three MOD operations. I thought this layout made more sense as the result of the MOD operation resides at the coordinates of its arguments.

Book1
	C	D	E	F	G	H	I	J	K
1	Sales Qty	Free Units			Qty \ Slabs	1050	530	110	40
2	3999	113			3999	849	319	99	19
3	660	18			660	660	130	20	20
4	5678	161			5678	428	428	98	18
5	480	13			480	480	480	40	0
6	9999	285			9999	549	19	19	19
Sheet2

Cell Formulas
Range	Formula
G2:G6	G2	=C2
H2:K6	H2	=MOD(G2,H$1)
D2:D6	D2	=SUMPRODUCT(QUOTIENT(+G2:J2,{1050,530,110,40}),{30,15,3,1})

 

[MosMon]

Greetings Mr. KRice,

I'm extremely thankful to you to get the solution. I can do a similar type of solution with the combination of Lookup, Vlookup, and IF. where 8 columns are required. Is it possible to find out the answer using a single column? Seeking your suggestion, please.
I'm really grateful to you again. Thank you

 

[KRice]

What version of Excel are you using? You should update your account profile with that information as some solution options depend on the version. I ask about the version because somehow you need to form an array of the remainders after each slab is evaluated, so recursion is necessary...the input for the next step depends on the result from the last step. If you have Excel 365, this may be doable in a single formula, but if you don't have 365, I don't see a way to do it without some helper cells. My approach above could be reduced to four columns wide as shown here, and those columns could be hidden. And as an example of doing this with a single formula in Excel 365, you could do something like I'm showing in D19:D23 using a LET function with SEQUENCE to construct the array...there may be a more elegant way to do that rather than nesting 4 IF statements, but it works!

MrExcel_20220607.xlsx
	C	D	E	F	G	H	I
10	Sales Qty	Free Units
11	3999	113		3999	849	319	99
12	660	18		660	660	130	20
13	5678	161		5678	428	428	98
14	480	13		480	480	480	40
15	9999	285		9999	549	19	19
16
17		single formula
18	Sales Qty	Free Units
19	3999	113
20	660	18
21	5678	161
22	480	13
23	9999	285
Sheet2

Cell Formulas
Range	Formula
F11:F15	F11	=C11
G11:G15	G11	=MOD(F11,1050)
H11:H15	H11	=MOD(G11,530)
I11:I15	I11	=MOD(H11,110)
D11:D15	D11	=SUMPRODUCT(QUOTIENT(+F11:I11,{1050,530,110,40}),{30,15,3,1})
D19:D23	D19	=SUMPRODUCT(QUOTIENT(LET(v,$C19,s,SEQUENCE(,4),IF(s=1,v,IF(s=2,MOD(v,1050),IF(s=3,MOD(MOD(v,1050),530),IF(s=4,MOD(MOD(MOD(v,1050),530),110)))))),{1050,530,110,40}),{30,15,3,1})

 

[MosMon]

Greetings Mr. KRice,

you're awesome.

Thanks for your suggestion. I have updated my office version which is 2019 and 2016 (Two different laptop).
I have tried your formula but not working. And I have tried many combinations based on your pattern but failed.

Thus, seeking your expert support, please

 

[KRice]

The single formula version will not work on versions earlier than 365, as the single formula uses functions (LET and SEQUENCE) and array forming techniques that aren't available/possible in 2016 or 2019. The 4-column helper version should work, I think. One thing to watch for: the QUOTIENT function needs that unary plus operator to allow the function to accept the range as an array...so it should be as shown QUOTIENT(+...

If that doesn't help, please post other details and the formula that you are using.

 

[kvsrinivasamurthy]

Try this

Condition			ColumnD
Sales Quantity	Free Unit
40​	1​
110​	3​
530​	15​
1050​	30​
ID of D	Distributor Name	Sales Quantity	Total Free Unit	Warehouse	Row8
0002532-2	Distributor 1	1660​	47	ABWW	Row9
0000874-2	Distributor 2	3480​	99	CLWW	Row10
0000852-2	Distributor 3	830​	23	ABWW	Row11
0000856-2	Distributor 4	220​	6	ABWW	Row12
0001714-2	Distributor 5	220​	6	CLWW	Row13
0002472-2	Distributor 6	202​	5	BGWW	Row14
0001856-2	Distributor 7	130​	3	ABWW	Row15
0002851-2	Distributor 8	120​	3	RHWW	Row16
0000881-2	Distributor 9	110​	3	SDWW	Row17
0001054-2	Distributor 10	90​	2	KHWW	Row18

In D9 copied down

=$B$6*INT(C9/$A$6)+$B$5*(INT(MOD(C9,$A$6)/$A$5))+$B$4*(INT(MOD(MOD(C9,$A$6),$A$5)/$A$4))+$B$3*INT(MOD(MOD(MOD(C9,$A$6),$A$5),$A$4)/$A$3)

 

[KRice]

As a variation on the nested MOD's with QUOTIENT or INT functions, you could eliminate the helper table altogether and hardwire the slab thresholds and free unit values directly in for a single formula approach with no other support cells needed:

MrExcel_20220607.xlsx
	C	D
30	Sales Qty	Free Units
31	3999	113
32	660	18
33	5678	161
34	480	13
35	9999	285
Sheet2

Cell Formulas
Range	Formula
D31:D35	D31	=30*INT(C31/1050)+15*(INT(MOD(C31,1050)/530))+3*(INT(MOD(MOD(C31,1050),530)/110))+1*INT(MOD(MOD(MOD(C31,1050),530),110)/40)

 

[KRice]

This slightly tighter version eliminates some unneeded characters in the formula (this is from cell D31 and refers to Sales Quantity in C31):

=30*INT(C31/1050)+15*INT(MOD(C31,1050)/530)+3*INT(MOD(MOD(C31,1050),530)/110)+INT(MOD(MOD(MOD(C31,1050),530),110)/40)