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

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