Running Balance in the same cell...

[tl]

Ok, I have a spreadsheet that I"m creating to keep track of spending that is
coming out of two different accounts. I want to be able to keep a running
balance but in the same cell based on a condition. Is this possible. My
spreadsheet looks like this.

Cell A Cell B Cell C Cell D
1 9000 3000
2 R 100
3 P 50
4 R 400
5 P 100

What I'm trying to do is that based on if "Cell A" = "R", I want to subtract
"Cell C" by 100 and then "Cell C" becomes "8900". Then If "Cell A" = "P", I
want to subtract "Cell D" by "50" and then "Cell D" becomes "2950". So, then
when the next "R" comes up, I want to take the new total of "8900" and
subtract "400" from it leaving me with "8500" and continue down the line. Is
this possible? What's the best way to go at this?

The formula I have only works with multiple cells doing running balances and
I don't want that.

TL

 

[Don Guillett]

=IF($A3<>"r","",C$2-SUMIF($A$2:$A3,"r",$B$2:$B3))
=IF($A3<>"p","",D$2-SUMIF($A$2:$A3,"r",$B$2:$B3))

 

[Ms-Exl-Learner]

If you just want to apply the formula for arriving the result in one cell
then follow the below steps:-

In C1 Cell paste the below formula
=9000-SUMIF(A:A,"R",B:B)

In D1 cell paste the below formula
=3000-SUMIF(A:A,"P",B:B)
Here the amounts are mentioned in the formula itself.


In another method if you want to see the results near by the B column Values
like if the B2 cell is R then it will deduct the B2 cell value from C1 cell
value and show you the remaining balance of C1 cell value nearby the cells
like Balance amount. For doing this follow the below steps:-

In C1 cell
9000

In D1 cell
3000

In C2 cell
=$C$1-SUMIF($A$2:$A2,"R",$B$2:$B2)
Copy the C2 and paste it for the remaining cells of C Column based on the
A&B Column Data.

In D2 Cell
=$D$1-SUMIF($A$2:$A2,"P",$B$2:$B2)

Copy the D2 and paste it for the remaining cells of D Column based on the
A&B Column Data.

Remember to Click Yes, if this post helps!