progressively sum cells and multiply by various amounts

[tHE fLY]

hi everyone,

I have 2 columns difference in amounts and PAID AMOUNTS

I want to progressively sum each cell in a column and when the total(s) exceed various amounts, I want to multiply by various %

i.e.

when difference is less than 50,000 x by .20 more than 50,000, multiply the difference in the corresponding cell by .35 more than 100,000 multiply cell by .40.. and so on

example

Column A Column B amounts paid--- difference amounts x certain %
difference

3000--- x .20
10000 x .20
15000 x .20
2500 --- total here is 50,500 multiple 2500 x .35

25000 -- x .35
20,000 x .35
10,000 -- total here is 105,500 multiply x .40


any help will be appreciated

 

[rilzniak]

=if(sum($a$1:$a1)>100000,$a1*0.4,if(and(sum($a$1:$a1)<100000,sum($a$1:$a1))>50000,$a1*0.35,$a1*0.2))

Enter that formula in a new column and drag it down.