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.

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

((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