Progressive Calculation

[Guest]

I'm trying to come up with a formula that will calculate a total from a table
so if you had a figure of 9600 and you compare it against the table
4000 0%
2000 10%
5000 20%
5000 30%
10000 40%
it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
3600*20%)

I found an excellent piece on Progressive Pricing on Chip Pearson's site but
despite a lot of tinkering I haven't been able to make it work for what I'm
trying to do above.

I'd be grateful for any ideas on how to do this.

Thanks a lot

 

[Rowan Drummond]

I think there is an error in the example you have given based on the
table and logic you have described. 9600 should return an answer of 920:

4000* 0% = 0
2000*10% = 200
3600*20% = 720
Total = 920

You have also not said what you want to do with numbers which are do not
fit into your table i.e anything over 26000 so I have let these error
out. With your table in cells A2:B6 and the value to check in A8 try the
formula:

=IF(A8<=A2,A8*B2,
IF(A8<=SUM(A2:A3),A2*B2+(A8-A2)*B3,
IF(A8<=SUM(A2:A4),A2*B2+A3*B3+(A8-SUM(A2:A3))*B4,
IF(A8<=SUM(A2:A5),A2*B2+A3*B3+A4*B4+(A8-SUM(A2:A4))*B5,
IF(A8<=SUM(A2:A6),A2*B2+A3*B3+A4*B4+A5*B5+(A8-SUM(A2:A5))*B6,
"Number Too Large")))))

Hope this helps
Rowan

 

[JMB]

Assuming your table is in cells A1:B5 and the value you are analyzing is in
cell B9, you could try:

=INDEX(A1:A5*B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))*INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)+1))

confirmed with Control+Shift+Enter after you type (or paste) it in.

Change cell references as needed.

 

[JMB]

Please disregard - there's an error in the formula.

 

[Biff]

Hi!

I get 920....

Try this:

=SUMPRODUCT(--(A1>{4000,6000,11000,16000}),(A1-{4000,6000,11000,16000}),{0.1,0.1,0.1,0.1})

Easier and more flexible if you setup a table. See this for examples:

http://mcgimpsey.com/excel/variablerate.html

Biff

 

[Roger Govier]

Hi

One way
=MAX(0,A1-4000)*10%+MAX(0,A1-6000)*10%+MAX(0,A1-11000)*10%+MAX(0,A1-16000)*10%-MAX(0,A1-116000)*40%

This formula takes cumulative 10%'s on each block of values up to 40%, but
as there is a cap (I assume from the table you posted) after 116,000 any
value above 116,000 has the cumulative percentage deducted.

Regards

Roger Govier