0

This is I think a simple problem but I can't seem to find the right solution for it. I don't know if VBA is needed for it. Basically I just want to subtract the last two non-empty cell in Excel. Example: I have the cell A1 and cell B1 and then subtract the value of A1 from B1 and place this value at the same row but another column C1 and so on. I have read this to get the last non-empty cell but I need two non-empty cell and perform operation on them. 

=LOOKUP(2,1/(A:A<>""),A:A)

Is there an easier way to do this? Or should I use VBA?

EDIT:

the reason is I'm making a somewhat similar to a balance sheet but a simple one, the user will just enter deposited value at the first column and expenses at the second column and then display the balance in the third column.. and vice versa.

EDIT: Sampleenter image description here

Community
  • 1
  • 1
silent_coder14
  • 583
  • 2
  • 10
  • 38

3 Answers3

1

Put this in C4 and copy down:

=SUM($A$3:$A4)-SUM($B$3:$B4)

enter image description here

If you want to copy the formula past the data so it will automatically fill when data is inserted you can do what @Dirk stated:

=IF(A4&B4<>"",SUM(A$3:A4)-SUM(B$3:B4),"")

Put it in C4 and copy down as far as desired.

This way you can fill the column with the formula and as the data is filled it will change from the empty string to the running total.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    to not get repeats after the last entry: `=IF(A3&B3<>"",SUM(A$3:A3)-SUM(B$3:B3),"")` in C3 and copy down ;) – Dirk Reichel Aug 15 '16 at 15:10
  • thank you but I want the third column not to change if I enter new values below.. – silent_coder14 Aug 15 '16 at 15:15
  • @silent_coder14 Did you try the formula in that situation? Does it change? It shouldn't – Ron Rosenfeld Aug 15 '16 at 15:17
  • The numbers below will not effect the numbers above. The formula only looks at the numbers above the row in which the formula is. As you can see from the picture, I placed the formula in C4 and copied down. The numbers in row 5 did not effect the formula in C4 and the numbers in row 6 did not efect the result in C4 and c5 – Scott Craner Aug 15 '16 at 15:18
  • @silent_coder14 I do not know why you are getting an error. It works in the above dataset. Do you have errors in your dataset? This will cause errors. It is the only thing I can think of. – Scott Craner Aug 15 '16 at 15:24
  • 1
    @silent_coder14 ahhhhh. You were using the formula I copied and pasted from Dirk. sometimes when copying from the comments unseen characters are inserted in the text. I have fixed it and made the reference to be put in C4 and copied down. See edit. You may need to refresh the screen. – Scott Craner Aug 15 '16 at 15:34
0

EDIT:

Regular formula:

=IF(ROW($C1)=1,OFFSET($C1,0,-2)-OFFSET($C1,0,-1),OFFSET($C1,-1,0)+OFFSET($C1,0,-2)-OFFSET($C1,0,-1))
Brian
  • 2,078
  • 1
  • 15
  • 28
-1

in cell C3 put "=A3-B3" in cell C4 put "=C3+A4-B4" then copy down. This will start the balance at 3000 in C3 then add any deposits or minus any withdrawals to the rest of column C.

user9175842
  • 1