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!

 

[myall_blues]

I’ve been looking at this but I’m struggling with the logic. Perhaps you can explain.
As you note, January is easy 35000 * 0.17 = $5950 ( possibly you have some extra decimals I can’t see).
Likewise February is ok - the cumulative value is 5000 over the first threshold, so 5000*0.13 plus (20,000-5000) remainder = 15000* 0.17.
But then we get to March. The cumulative volume is 75,000, which is 25,000 over threshold but not in the next threshold. So using the same logic as February we would calculate 25,000 (the amount over threshold) * 0.13, plus (20,000-25,000) remainder = -5000*0.17??
This doesn’t seem to make sense calculating a negative value. The issue gets progressively worse as the cumulative total gets bigger, but the monthly values don’t really vary too much from one month to the next.

 

[Green Lantern]

Thank you kindly for responding and looking at this...Murray! you are correct up to February, in March volume is 20,000 and because we are over the 50k and completely within the threshold between (50,0000-100,000) we multiply 20,000 *0.13. As we get to April volume 10,000 over the 100k threshold therefore 10,000*0.12 + (35,000-10,000) * 0.13= your cost. As you can see it is not that easy to derive a formula based on the current month volumes and where part of that volume may fall under two different thresholds. I hope I'm not confusing things...Any assistance is immensely appreciated.

 

[Green Lantern]

I would need a formula that would look at cumulative volume to compare against threshold and at the same time ensure monthly volumes are calculated correctly with the correct tiered cost.

 

[hagia_sofia]

Hello, I would add one more question to the pool: how the calculation should proceed when the cumulative total crosses the last threshold (250000), i.e. what happens from August onwards?

 

[hagia_sofia]

Hello, here is a rather complicated attempt and at the moment it will reutrn an error from September to Decemeber as there is no value for cumulative total that is higher than 250000 (a thing that you will need to decide):

=LET(
lower,A1:A5,
upper,B1:B5,
cost,C1:C5,
monthly,B9:M9,
cumulative,B10:M10,
subtraction,cumulative-monthly,
a_1,MAP(cumulative,LAMBDA(x,FILTER(lower,(lower<=x)*(upper>=x)))),
a_2,MAP(subtraction,LAMBDA(x,FILTER(lower,(lower<=x)*(upper>=x)))),
b_1,XLOOKUP(a_1,lower,cost),
b_2,XLOOKUP(a_2,lower,cost),
c,a_1=a_2,
d_1,IF(c=FALSE,cumulative-a_1+1,0),
d_2,IF(c=TRUE,monthly,monthly-d_1),
(b_2*d_2)+(b_1*d_1))

 

[Green Lantern]

Apologies for not ensuring a wider threshold. I will review the above.

 

[Green Lantern]

Thank you for putting this complicated formula together you must be a whiz. I am certainly not at your level. and do have some dumb questions...

what does a_1 refer to? And when you display x values which one in particular? Also you have a_2,MAP (subtraction,....what do you mean here?

Also, do I apply this formal to each cell i.e., b12, c12, d13, and so on? Apologies this is interesting what you have put together..and I love to understand it more.

 

[hagia_sofia]

Thank you for putting this complicated formula together you must be a whiz. I am certainly not at your level. and do have some dumb questions...

what does a_1 refer to? And when you display x values which one in particular? Also you have a_2,MAP (subtraction,....what do you mean here?

Also, do I apply this formal to each cell i.e., b12, c12, d13, and so on? Apologies this is interesting what you have put together..and I love to understand it more.

Hello, I will try to explain:

LET function allows us to define a name for any part of a formula and use the name as a shortcut of that particular part; the name could have a meaning or be just a "randomly" chosen chain.

So, "subtraction" represents the result of subtraction of cumulative amount and monthly amount. The idea behind it is to verify whether the whole monthly amount belongs to the same interval or not (more on that later), i.e. whether the relevant monthly amount needs to be distributed or not.

a_1 (a chosen, "random" name) returns the lower bound of the interval into which the cumulative amount belongs in a particular month, a_2 does the same for "subtraction" (cf. above).

Based on a_1 and a_2, b_1 and b_2 assign the relevant cost corresponding with the particular interval.

The letter "c" verifies whether identified lower bounds (i.e. a_1 and a_2) are the same or not, i.e. whether there is a need to apply only one cost or two costs.

Based on "c", d_1 and d_2 distribute the relevant monthly amount, i.e. calculate the amounts where there is only one cost applied and where there are two costs applied.

The last part of the formula multiplies d_1 and d_2 amounts by the respective costs.

As far as "x" is concerned it represents every value of "cumulative" and "subtraction"; as for the "do I apply this formal to each cell i.e., b12, c12, d13, and so on?" part I am not sure what is the answer because I do not know which data you have in those cells; but generally speaking the formula should calculate for the whole row at once (January - December).

You can check what every part of the calculation does if you name the last part of the formula, e.g. z,(b_2*d_2)+(b_1*d_1), and then use any name behind it, i.e. z,(b_2*d_2)+(b_1*d_1),a_1 will return a_1 part of the calculation and so on.

I now realize that I have not considered a situation if the monthly amount is so high that it belongs to three (or more) intervals; so, a) is it possible? b) if yes, how the calculation should proceed?

Also, one more thing to be addressed is that the intervals should be continuous (at the moment there is a gap between 251000-300000 and 351000-400000) + it would be easier (as far as formula construction is concerned) if you could also set the top limit (e.g. some high number like 1 billion).

So, I guess this is not a finished story yet

 

[Sanjeev1976]

You can also try the below :

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	0	50,000	17.0%
2	50,001	100,000	13.0%
3	100,001	150,000	12.0%
4	150,001	200,000	11.0%
5	200,001	250,000	10.0%
6	250,001	300,000	9.0%
7	300,001	350,000	8.0%
8	350,001	>	7.0%
9
10		January	February	March	April	May	June	July	August	September	October	November	December
11	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
12	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
13	Cost	5,950	3,200	2,600	4,450	4,320	3,120	4,160	4,150	2,250	1,800	3,200	1,920
Sheet2

Cell Formulas
Range	Formula
B2:B7	B2	=A2+50000-1
A2:A8	A2	=B1+1
B12:M12	B12	=SCAN(0,B11:M11,LAMBDA(a,b,a+b))
B13:M13	B13	=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.