Calculation based on previous transactions

[punnu07]

Hi,

I have a problem to solve.

I have a data set of 7000 transactions and I need to calculate the amount for each transaction as per below formula. Please note that calculation will be done basis of account number.

Account Number	Value	Percentage	Amount
ABC	1000	0.1	=(B2)*C2-0
ABC	1000	0.15	=(B3+B2)*C3-(D2)
ABC	1000	0.05	=(B4+B3+B2)*C4-(D3+D2)
DEF	1000	0.1	=(B5)*C5-0
DEF	1000	0.15	=(B6+B5)*C6-(D5)
DEF	1000	0.05	=(B7+B6+B5)*C7-(D6+D5)
DEF	1000	0.1	=(B8+B7+B6+B5)*C8-(D7+D6+D5)
QWE	1000	0.15	=(B9)*C9-0
QWE	1000	0.05	=(B10+B9)*C10-(D9)
QWE	1000	0.1	=(B11+B10+B9)*C11-(D10+D9)
QWE	1000	0.15	=(B12+B11+B10+B9)*C12-(D11+D10+D9)
QWE	1000	0.05	=(B13+B12+B11+B10+B9)*C13-(D12+D11+D10+D9)
QWE	1000	0.1	=(B14+B13+B12+B11+B10+B9)*C14-(D13+D12+D11+D10+D9)

<tbody>
</tbody>

((Value of current transaction + value of all previous transactions) x NEW percentage%) - (sum of all previous values)

Can anyone please help me with a generic excel formula which can be put in all 7000 transactions or any VBA code.

Thank you in advance.

 

[AlanY]

put B2*C2 in D2, and

=C3*SUMIF($A$2:A3,A3,$B$2:B3)-SUMIF($A$2:A2,A3,$D$2:D2) in D3, copy down

 

[punnu07]

Thank you Alan for your help. You are great.

 

[AlanY]

you're welcome