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

[TABLE="width: 712"]
<tbody>[TR]
[TD]Unit
[/TD]
[TD]Unit Price(USD)
[/TD]
[TD]Step
[/TD]
[/TR]
[TR]
[TD]1≤ Unit≤16
[/TD]
[TD]50
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]17≤ Unit≤32
[/TD]
[TD]75
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]33≤ Unit≤48
[/TD]
[TD]100
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]49≤ Unit≤64
[/TD]
[TD]125
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]65≤ Unit≤74
[/TD]
[TD]150
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]RRC Unit≥75
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
Many thanks[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Lower​

[/TD]
[TD]

Higher​

[/TD]
[TD]

Price​

[/TD]
[TD]

Marginal​

[/TD]
[TD][/TD]
[TD]

Units​

[/TD]
[TD]

Cost​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

16​

[/TD]
[TD]

50​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]

50​

[/TD]
[TD]

3850​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

17​

[/TD]
[TD]

32​

[/TD]
[TD]

75​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

33​

[/TD]
[TD]

48​

[/TD]
[TD]

100​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

49​

[/TD]
[TD]

64​

[/TD]
[TD]

125​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

65​

[/TD]
[TD]

74​

[/TD]
[TD]

150​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

75​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

-147​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.