Progressive calculation - Not % based

[wit1111]

Hi all,

Hoping you can please help, I have reviewed many of the proposed solutions to a progressive calculation I am trying to solve but am not winning (or looking hard enough). The solutions I have found as far deal mainly with percentages which is not working completely.

I have a progressive billing as such: if I have 50 units, the cost is split as follows: first 16 x $50, then the next 16 would fall into the $75 bracket, next 16 in $100, remaining 2 in $125. Is there a dynamic function to cater for this. Table breakdown as follows

Unit	Unit Price(USD)	Step
1≤ Unit≤16	50	16
17≤ Unit≤32	75	16
33≤ Unit≤48	100	16
49≤ Unit≤64	125	16
65≤ Unit≤74	150	10
RRC Unit≥75	3
Many thanks

<tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this

	A​	B​	C​	D​	E​	F​	G​
1​	Lower​	Higher​	Price​	Marginal​		Units​	Cost​
2​	1​	16​	50​	50​		50​	3850​
3​	17​	32​	75​	25​
4​	33​	48​	100​	25​
5​	49​	64​	125​	25​
6​	65​	74​	150​	25​
7​	75​		3​	-147​

<tbody>
</tbody>

Given: columns A:C; F2

Formula in D2 copied down
=C2-N(C1)

Formula in G2
=SUMPRODUCT(--(F2>=A2:A7),F2+1-A2:A7,D2:D7)

Hope this helps

M.
ps: for a explanation how the formula works, take a look at
http://www.mcgimpsey.com/excel/variablerate.html

 

[wit1111]

Brilliant, thank you

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.