Cost per thresholds

Green Lantern

New Member
Joined
Aug 24, 2024
Messages
13
Office Version
  1. 365
Platform
  1. MacOS
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!


1724540543779.png
 
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
ABCDE
1Version bCumulativeMonthly
2Sales
3Jan-202435,0005,950.005,950.005,950.00
4Feb-202420,0003,200.009,150.003,200.00
5Mar-202450,0006,450.0015,600.006,450.00
6Apr-202460,0007,050.0022,650.007,050.00
7May-202470,0007,350.0030,000.007,350.00
8Jun-202480,0007,200.0037,200.007,200.00
9Jul-202490,0007,200.0044,400.007,200.00
10Aug-2024100,0008,000.0052,400.008,000.00
11Sep-2024110,0008,800.0061,200.008,800.00
12Oct-2024120,0009,600.0070,800.009,600.00
13Nov-2024130,00010,400.0081,200.0010,400.00
14Dec-2024300,00024,000.00105,200.0024,000.00
15105,200.00
16
17Total1,165,000105,200.00
18
19
20BracketsRates
21
220.0017%
2350,000.0013%
24100,000.0012%
25150,000.0011%
26200,000.0010%
27250,000.009%
28300,000.008%
29
Sheet1
Cell Formulas
RangeFormula
D3:D14D3=Pricing_a(SUM($B$2:B3))
E3:E14E3=Pricing_a(SUM($B$2:B3))-SUM($E$2:E2)
C3:C14C3=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))
C15C15=SUM(C3:C14)
B17B17=SUM(B3:B14)
C17C17=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
NameFormula
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)))
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.

1724729016723.png



1724728958619.png
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJKLM
1050,00016.92%
250,001100,00012.75%
3100,001150,00011.92%
4150,001200,00011.08%
5200,001250,0009.67%
6250,001350,0009.00%
7350,001425,0008.00%
8
9JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
10Monthly Volume35,00020,00020,00035,00036,00028,00039,00042,00025,00020,00040,00026,000
11Cumulative Volume35,00055,00075,000110,000146,000174,000213,000255,000280,000300,000340,000366,000
12Solution 15,9213,1752,5504,3794,2903,1374,1384,0272,2501,8003,6002,180
13Solution 25,9213,1752,5504,3794,2903,1374,1384,0272,2501,8003,6002,180
Sheet1
Cell Formulas
RangeFormula
A2:A7A2=B1+1
B2:B5B2=A2+50000-1
B6B6=A6+100000-1
B7B7=A7+75000-1
B11:M11B11=SCAN(0,B10:M10,LAMBDA(a,b,a+b))
B12:M12B12=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)) )
B13B13=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:M13C13=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.
 
Upvote 0
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:A7A2=B1+1
B2:B5B2=A2+50000-1
B6B6=A6+100000-1
B7B7=A7+75000-1
 
Upvote 0
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)) )
 
Upvote 0
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...

1724811298312.png

what would I have to do

A2:A7A2=B1+1
B2:B5B2=A2+50000-1
B6B6=A6+100000-1
B7B7=A7+75000-1
 
Upvote 0
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.
 
Upvote 0
I get the result 3175 and not 3383. And instead of copying each formula, you can copy the entire data and then check.

Book4
ABCDEFGHIJKLM
1016.92%
2050,00016.92%-4.17%
350,001100,00012.75%-0.83%
4100,001150,00011.92%-0.83%
5150,001200,00011.08%-1.42%
6200,001250,0009.67%-1.67%
7250,001300,0008.00%-1.00%
8300,001500,0007.00%-1.00%
9500,001501,0006.00%-6.00%
10
11JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
12Monthly Volume35,00020,00020,00035,00036,00028,00039,00042,00025,00020,00040,000226,000
13Cumulative Volume35,00055,00075,000110,000146,000174,000213,000255,000280,000300,000340,000566,000
14Solution 15,9213,1752,5504,3794,2903,1374,1383,9772,0001,6002,80011,260
15Solution 25,9213,1752,5504,3794,2903,1374,1383,9772,0001,6002,80011,260
Sheet1
Cell Formulas
RangeFormula
B3:B7B3=A3+50000-1
B8B8=A8+200000-1
A3:A9A3=B2+1
D1D1=C2
D2:D9D2=C3-C2
B13:M13B13=SCAN(0,B12:M12,LAMBDA(a,b,a+b))
B14:M14B14=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)) )
B15B15=SUMPRODUCT(--(B13>HSTACK($B$1:$B$9)),(B13-HSTACK($B$1:$B$9)),HSTACK($D$1:$D$9))
C15:M15C15=SUMPRODUCT(--(C13>HSTACK($B$1:$B$9)),(C13-HSTACK($B$1:$B$9)),HSTACK($D$1:$D$9))-SUM($B15:B15)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top