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("A 1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATC H(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))* INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&RO W(A1:A5))),1)+1))

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

Change cell references as needed.




"nospaminlich" wrote:

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