Cost per thresholds

G

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
 
Sort by date Sort by votes
Here is another aproach:

You only need the table with upper limit of each band.

CostThresholds.xlsx
ABCDEFGHIJKLMN
1
250,00017.0%
3100,00013.0%
4150,00012.0%
5200,00011.0%
6250,00010.0%
7300,0009.0%
8350,0008.0%
9inf7.0%
10
11JanFebMarAprMayJunJulAugSepOctNovDecTotal
1235,000.0020,000.0020,000.0035,000.0036,000.0028,000.0039,000.0042,000.0025,000.0020,000.0040,000.0026,000.00366,000.00
1335,000.0055,000.0075,000.00110,000.00146,000.00174,000.00213,000.00255,000.00280,000.00300,000.00340,000.00366,000.00
14
15Cost595032002600445043203120416041502250180032001920
Sheet4
Cell Formulas
RangeFormula
N12N12=SUM(B12:M12)
B13:M13B13=B12+A13
B15:M15B15=LET(s,N(A13), e, B13, th_rs, VSTACK(0, DROP($B$2:$B$9, -1)), th_re, VSTACK(DROP(th_rs, 1), 1E+300), th_v, $C$2:$C$9, seq, SEQUENCE(ROWS(th_v)), i_s, SUM((s<=th_re)*(s>th_rs)*seq), i_e, SUM((e<=th_re)*(e>th_rs)*seq), temp, VSTACK(i_s, i_e), t, HSTACK(th_rs, th_re, th_v), ft, FILTER(t, (seq>=i_s)*(seq<=i_e)), SUM(BYROW(ft, LAMBDA(x, (MIN(INDEX(x, 1, 2), e)-MAX(INDEX(x, 1,1), s))*INDEX(x, 1, 3)))) )


It considers the case where you may have a month where the cumulative volume falls in more than 2 of your bands.
For example if you have (I'm exaggerating) a month with 150000 volume like this:

1724863837886.png


And it take 4 bands:

1724863866324.png

It will calculate the cost correctly (I think, you should test it further).

Here is the working file to download:
CostThresholds.zip
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Dave Patton said:
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)))
Click to expand...
Sanjeev1976 said:
You can also try the below :

Book2
ABCDEFGHIJKLM
1050,00017.0%
250,001100,00013.0%
3100,001150,00012.0%
4150,001200,00011.0%
5200,001250,00010.0%
6250,001300,0009.0%
7300,001350,0008.0%
8350,001>7.0%
9
10JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
11Monthly Volume35,00020,00020,00035,00036,00028,00039,00042,00025,00020,00040,00026,000
12Cumulative Volume35,00055,00075,000110,000146,000174,000213,000255,000280,000300,000340,000366,000
13Cost5,9503,2002,6004,4504,3203,1204,1604,1502,2501,8003,2001,920
Sheet2
Cell Formulas
RangeFormula
B2:B7B2=A2+50000-1
A2:A8A2=B1+1
B12:M12B12=SCAN(0,B11:M11,LAMBDA(a,b,a+b))
B13:M13B13=LET( lower_limit, A1:A8, upper_limit, B1:B8, cost_bracket, C1:C8, volume, SCAN(0,B11:M11,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)) )
Dynamic array formulas.
Click to expand...
Hi Sanjeev thank you so much for putting this together. This is brilliant..how do you adjust this formula so that anything over 500,000 volume is calculated at the .06 meaning the last tier the price remains the same regardless of how high the volume goes.Thank you kindly!
 
Upvote 0
Sanjeev1976 said:
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.
Click to expand...
Hi Sanjeev thank you so much for putting this together. This is brilliant..how do you adjust this formula so that anything over 500,000 volume is calculated at the .06 meaning the last tier the price remains the same regardless of how high the volume goes.Thank you kindly!
 
Upvote 0
You could try the formula that I suggested in post #11.
I do not know the brackets and rates that are relevant; various information is in the posts.
If you have just one or a just a few rate structures, brackets and rates can be built into the formula.


Please post the relevant information with the forum's tool named Xl2BB.
 
Upvote 0
T202408b.xlsx
ABCDEFGHIJKLM
10JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
11Monthly Volume35,000.0020,000.0020,000.0035,000.0036,000.0028,000.0039,000.0042,000.00200,000.00300,000.00400,000.00800,000.00
155,950.003,200.002,600.004,450.004,320.003,120.004,160.004,150.0015,400.0021,000.0028,000.0056,000.00
16
17
18Total1,955,000.00152,350.00152,350.00
19152,350.00
20
Sheet5
Cell Formulas
RangeFormula
B15:M15B15=Cost_a(SUM($B$11:B11))-SUM($A$15:A15)
B18B18=SUM(B11:M11)
D18D18=LET(b,{0;50000;100000;150000;200000;250000;300000;350000},r,{0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01;-0.01},SUM((B18>b)*(B18-b)*r))
C18C18=Cost_a(B18)
C19C19=SUM(B15:M15)
Lambda Functions
NameFormula
Cost_a=LAMBDA(CumRev,LET(b, {0;50000;100000;150000;200000;250000;300000;350000}, r, {0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01;-0.01}, SUM((CumRev > b) * (CumRev - b) * r)))
 
Upvote 0
Green Lantern said:
Hi Sanjeev thank you so much for putting this together. This is brilliant..how do you adjust this formula so that anything over 500,000 volume is calculated at the .06 meaning the last tier the price remains the same regardless of how high the volume goes.Thank you kindly!
Click to expand...

You can just change the number highlighted in yellow to any high value figure :

Book9
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,0015,000,0006.00%-6.00%
10
11JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
12Monthly Volume35,00020,00020,00035,00036,00028,00039,00042,00025,00020,00040,00026,000
13Cumulative Volume35,00055,00075,000110,000146,000174,000213,000255,000280,000300,000340,000366,000
14Solution 15,9213,1752,5504,3794,2903,1374,1383,9772,0001,6002,8001,820
15Solution 25,9213,1752,5504,3794,2903,1374,1383,9772,0001,6002,8001,820
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
A post with a variety of solutions that you can try.
T202408b.xlsx
ABCDEFGHIJKLM
1JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
2Monthly Volume35,000.0020,000.0020,000.0035,000.0036,000.0028,000.0039,000.0042,000.00100,000.00100,000.00100,000.00100,000.00
65,950.003,200.002,600.004,450.004,320.003,120.004,160.004,150.008,400.007,000.007,000.007,000.00
7
8
9Total655,000.0061,350.0061,350.0061,350.0061,350.0061,350.0061,350.00
10
11BracketsRates
120.0017%
1350,000.0013%
14100,000.0012%
15150,000.0011%
16200,000.0010%
17250,000.009%
18300,000.008%
19350,000.007%
20
Cost2
Cell Formulas
RangeFormula
B6:M6B6=Cost_a(SUM($B$2:B2))-SUM($A$6:A6)
B9B9=SUM(B2:M2)
C9C9=Cost_a(B9)
D9D9=LET(b,{0;50000;100000;150000;200000;250000;300000;350000},r,{0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01;-0.01},SUM((B9>b)*(B9-b)*r))
E9E9=CostX(A12:B19,B9)
F9F9=SUM(B6:M6)
G9G9=LET(rng,A12:B19,i,B9,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro)))
I9I9=SUM((B9>aB)*(B9-aB)*aR)
Lambda Functions
NameFormula
Cost_a=LAMBDA(CumRev,LET(b, {0;50000;100000;150000;200000;250000;300000;350000}, r, {0.17;-0.04;-0.01;-0.01;-0.01;-0.01;-0.01;-0.01}, SUM((CumRev > b) * (CumRev - b) * r)))
CostX=LAMBDA(rngRateData,CumulativeAmts,LET(rng,rngRateData,i,CumulativeAmts,b,CHOOSECOLS(rng,1),r,CHOOSECOLS(rng,2),ro,VSTACK(0,DROP(r,-1)),SUM((i>b)*(i-b)*(r-ro))))


FormulaI9
- self contained
- includes a named array of the brackets [aB] and a named array for the rate differentials [aR]
LambdaE9
- input required is the range of bracket and rate information and the relevant value aggregate or monthly.
LambdaC9
- input required is the relevant value aggregate or monthly.
- the bracket and rate differential arrays are in the formula
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Water Bill Calculator w/ROI from Percentage Savings
Replies
1
Views
369
Jared016
J
G
  • Question
IF Formula...????
Replies
1
Views
332
Peter_SSs
Peter_SSs
M
  • Question
Year wise calculation
Replies
4
Views
897
Anthony47
A
S
  • Question
Tranched Quantities Logic Problem
Replies
1
Views
537
KRice
K
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
977
AndyEd
A

Forum statistics

Threads
1,221,508
Messages
6,160,222
Members
451,631
Latest member
coffiajoseph

Share this page

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