Cost per thresholds

[Green Lantern]

I have a situation where I need to find a formula where each month cumulative volume dictates which cost will be applied. For example January is simple the volume is less 50,000, therefore 35,000*0.17 is calculated at $5930,83...the problem I am having is the next month February 20,000 where the cumulated volume is over 50,000 (55000) but only 5000 is applied to the next threshold cost 0.13 cents, however what also has to be calculated is 20,000-5000 * $0.17 because the remainder is actually below the 50,000 threshold. Not sure if this make but any help would be greatly appreciated!

 

[Dave Patton]

You can edit the following to consider your source information; you can use it with accumulated sales or monthly sales.
If you have a variety of brackets and/or rates you can update this approach with other Lambda functions.
The brackets are from the table and the rates are the rate differentials.

T202408b.xlsx
	A	B	C	D	E
1			Version b	Cumulative	Monthly
2		Sales
3	Jan-2024	35,000	5,950.00	5,950.00	5,950.00
4	Feb-2024	20,000	3,200.00	9,150.00	3,200.00
5	Mar-2024	50,000	6,450.00	15,600.00	6,450.00
6	Apr-2024	60,000	7,050.00	22,650.00	7,050.00
7	May-2024	70,000	7,350.00	30,000.00	7,350.00
8	Jun-2024	80,000	7,200.00	37,200.00	7,200.00
9	Jul-2024	90,000	7,200.00	44,400.00	7,200.00
10	Aug-2024	100,000	8,000.00	52,400.00	8,000.00
11	Sep-2024	110,000	8,800.00	61,200.00	8,800.00
12	Oct-2024	120,000	9,600.00	70,800.00	9,600.00
13	Nov-2024	130,000	10,400.00	81,200.00	10,400.00
14	Dec-2024	300,000	24,000.00	105,200.00	24,000.00
15			105,200.00
16
17	Total	1,165,000	105,200.00
18
19
20	Brackets	Rates
21
22	0.00	17%
23	50,000.00	13%
24	100,000.00	12%
25	150,000.00	11%
26	200,000.00	10%
27	250,000.00	9%
28	300,000.00	8%
29
Sheet1

Cell Formulas
Range	Formula
D3:D14	D3	=Pricing_a(SUM($B$2:B3))
E3:E14	E3	=Pricing_a(SUM($B$2:B3))-SUM($E$2:E2)
C3:C14	C3	=LET(s,SUM($B$2:B3),b,{0;50000;100000;150000;200000;250000;300000},r,{0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01},SUM((s>b)*(s-b)*r)-SUM($C$2:C2))
C15	C15	=SUM(C3:C14)
B17	B17	=SUM(B3:B14)
C17	C17	=LET(s,B17,b,{0;50000;100000;150000;200000;250000;300000},r,{0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01},SUM((s>b)*(s-b)*r))

Lambda Functions
Name	Formula
Pricing_a	=LAMBDA(CumRev,LET(b, {0;50000;100000;150000;200000;250000;300000}, r, {0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01}, SUM((CumRev > b) * (CumRev - b) * r)))

 

[Green Lantern]

Thank you for the solution above I will look at this..however I feel I may not have explained it correctly.

The below is what I am aiming for where the yellow A12:M:12 depicts calculations/solution and the grey field A10:M10 volumes per month and A11:M11 is the cumulative volume that is measured against the table for tiered cost. I'd like a formula that would calculate for each cell highlight yellow as shown below but to take into considering the correct costing if it falls between thresholds. I have listed my formulas below but the drawback here is that every time I change the values per month Grey A10:M10 the calculations have to be adjusted manually.

 

[Green Lantern]

Thank you for the solution above I will look at this..however I feel I may not have explained it correctly.

The below is what I am aiming for where the yellow A12:M:12 depicts calculations/solution and the grey field A10:M10 volumes per month and A11:M11 is the cumulative volume that is measured against the table for tiered cost. I'd like a formula that would calculate for each cell highlight yellow as shown below but to take into considering the correct costing if it falls between thresholds. I have listed my formulas below but the drawback here is that every time I change the values per month Grey A10:M10 the calculations have to be adjusted manually.

View attachment 115989


View attachment 115988

 

[Dave Patton]

Please show your arithmetic for the first couple of calculations.
We may be able to help you if you post an extract of your sheet with the forum's tool named Xl2BB.

You can post the sample suggestions to a clear sheet.
Click on the icon below the F(x) in the heading, move to your sheet's cell A1 and paste.
Review the formula.

 

[Sanjeev1976]

Thank you for the solution above I will look at this..however I feel I may not have explained it correctly.

The below is what I am aiming for where the yellow A12:M:12 depicts calculations/solution and the grey field A10:M10 volumes per month and A11:M11 is the cumulative volume that is measured against the table for tiered cost. I'd like a formula that would calculate for each cell highlight yellow as shown below but to take into considering the correct costing if it falls between thresholds. I have listed my formulas below but the drawback here is that every time I change the values per month Grey A10:M10 the calculations have to be adjusted manually.

View attachment 115989


View attachment 115988

Hello Green Lantern,

You can try the below solution :

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0	50,000	16.92%
2	50,001	100,000	12.75%
3	100,001	150,000	11.92%
4	150,001	200,000	11.08%
5	200,001	250,000	9.67%
6	250,001	350,000	9.00%
7	350,001	425,000	8.00%
8
9		January	February	March	April	May	June	July	August	September	October	November	December
10	Monthly Volume	35,000	20,000	20,000	35,000	36,000	28,000	39,000	42,000	25,000	20,000	40,000	26,000
11	Cumulative Volume	35,000	55,000	75,000	110,000	146,000	174,000	213,000	255,000	280,000	300,000	340,000	366,000
12	Solution 1	5,921	3,175	2,550	4,379	4,290	3,137	4,138	4,027	2,250	1,800	3,600	2,180
13	Solution 2	5,921	3,175	2,550	4,379	4,290	3,137	4,138	4,027	2,250	1,800	3,600	2,180
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=B1+1
B2:B5	B2	=A2+50000-1
B6	B6	=A6+100000-1
B7	B7	=A7+75000-1
B11:M11	B11	=SCAN(0,B10:M10,LAMBDA(a,b,a+b))
B12:M12	B12	=LET( lower_limit, A1:A7, upper_limit, B1:B7, cost_bracket, C1:C7, volume, SCAN(0,B10:M10,LAMBDA(a,b,a+b)), volume_by_bracket, IF(volume<=lower_limit,0,IF(volume > upper_limit,upper_limit - lower_limit,volume - lower_limit)), cost_calc, volume_by_bracket*cost_bracket, a, BYCOL(cost_calc,LAMBDA(x,SUM(x))), b, DROP(a,,1), HSTACK(TAKE(a,,1),b-DROP(a,,-1)) )
B13	B13	=SUMPRODUCT(--(B11>{0;50000;100000;150000;200000;250000;350000;425000}),(B11-{0;50000;100000;150000;200000;250000;350000;425000}),{0.169166666666667;-0.041666666666667;-0.008333333333333;-0.008333333333334;-0.014166666666666;-0.006666666666667;-0.01;-0.01})
C13:M13	C13	=SUMPRODUCT(--(C11>{0;50000;100000;150000;200000;250000;350000;425000}),(C11-{0;50000;100000;150000;200000;250000;350000;425000}),{0.169166666666667;-0.041666666666667;-0.008333333333333;-0.008333333333334;-0.014166666666666;-0.006666666666667;-0.01;-0.01})-SUM($B13:B13)
Dynamic array formulas.

 

[Green Lantern]

This is fantastic, I appreciate the work here. I tried cutting and pasting the formula

B11	=SCAN(0,B10:M10,LAMBDA(a,b,a+b))

)and I received an error spill message. I simply used your B13 formula and pasted that into the B12 cell for which it did the right calculation. However I still have the spill error message..any assistance is appreciated.

I had Alsop ensured the below was completed.

A2:A7	A2	=B1+1
B2:B5	B2	=A2+50000-1
B6	B6	=A6+100000-1
B7	B7	=A7+75000-1

 

[Green Lantern]

Disregard above message...Thank you, it worked!! I used the second formula..seems simpler.
I could not interpret this part of the 1st formal, this is not a direct cut and paste. :
F(volume<=lower_limit,0,IF(volume > upper_limit,upper_limit - lower_limit,volume - lower_limit)), cost_calc, volume_by_bracket*cost_bracket, a, BYCOL(cost_calc,LAMBDA(x,SUM(x))), b, DROP(a,,1), HSTACK(TAKE(a,,1),b-DROP(a,,-1)) )

 

[Green Lantern]

One more question if I have to change the volume thresholds, do I need to set up differently?

Say I use this threshold: A little different from than the example...

what would I have to do

A2:A7	A2	=B1+1
B2:B5	B2	=A2+50000-1
B6	B6	=A6+100000-1
B7	B7	=A7+75000-1

 

[Green Lantern]

I dragged the formula B13 across it and if you try you will see the calculation is incorrect. I get a total of $3383 for February. It should be $3175. See above original post re formulas. The accumulated amount falls between 0-50000 and 500001-100000, so (55000-50000)*0.13 + 20000-(55000-5000)*0.13 = $3175.

 

[Sanjeev1976]

I get the result 3175 and not 3383. And instead of copying each formula, you can copy the entire data and then check.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		0		16.92%
2	0	50,000	16.92%	-4.17%
3	50,001	100,000	12.75%	-0.83%
4	100,001	150,000	11.92%	-0.83%
5	150,001	200,000	11.08%	-1.42%
6	200,001	250,000	9.67%	-1.67%
7	250,001	300,000	8.00%	-1.00%
8	300,001	500,000	7.00%	-1.00%
9	500,001	501,000	6.00%	-6.00%
10
11		January	February	March	April	May	June	July	August	September	October	November	December
12	Monthly Volume	35,000	20,000	20,000	35,000	36,000	28,000	39,000	42,000	25,000	20,000	40,000	226,000
13	Cumulative Volume	35,000	55,000	75,000	110,000	146,000	174,000	213,000	255,000	280,000	300,000	340,000	566,000
14	Solution 1	5,921	3,175	2,550	4,379	4,290	3,137	4,138	3,977	2,000	1,600	2,800	11,260
15	Solution 2	5,921	3,175	2,550	4,379	4,290	3,137	4,138	3,977	2,000	1,600	2,800	11,260
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	=A3+50000-1
B8	B8	=A8+200000-1
A3:A9	A3	=B2+1
D1	D1	=C2
D2:D9	D2	=C3-C2
B13:M13	B13	=SCAN(0,B12:M12,LAMBDA(a,b,a+b))
B14:M14	B14	=LET( lower_limit, A2:A9, upper_limit, B2:B9, cost_bracket, C2:C9, volume, SCAN(0,B12:M12,LAMBDA(a,b,a+b)), volume_by_bracket, IF(volume<=lower_limit,0,IF(volume > upper_limit,upper_limit - lower_limit,volume - lower_limit)), cost_calc, volume_by_bracket*cost_bracket, a, BYCOL(cost_calc,LAMBDA(x,SUM(x))), b, DROP(a,,1), HSTACK(TAKE(a,,1),b-DROP(a,,-1)) )
B15	B15	=SUMPRODUCT(--(B13>HSTACK($B$1:$B$9)),(B13-HSTACK($B$1:$B$9)),HSTACK($D$1:$D$9))
C15:M15	C15	=SUMPRODUCT(--(C13>HSTACK($B$1:$B$9)),(C13-HSTACK($B$1:$B$9)),HSTACK($D$1:$D$9))-SUM($B15:B15)
Dynamic array formulas.