# How to get a running balance?

I'm messing around with Google Sheets today, and I'm trying to put together a balance sheet similar to the one my bank uses. Its got both a credit and debit field in each row and a running balance to the far right. I'm kinda new to this, so I'm learning as I go.

I've got it set up like so - cell E2 has the following formula:

``````=sum(c2:d2)
``````

While cells E3 and beyond have:

``````=(C4*-1)+D4+E3
`````` It works for the most part, but if you'll notice cells E6 onwards each have the same value as a result of the formula being applied to every cell in that column. Is there any way to hide the balance value or change it to 0 if the debit/credit field is empty, just for layout purposes?

edit: It's supposed to be a running balance of an account. So the end result is like this

``````DEBIT     CREDIT     BALANCE     DATE
5          5           05 Feb 2015
1                    4           06 Feb 2015
10         14          07 Feb 2015
8                    6           09 Feb 2015
``````

But just to re-iterate, my issue isn't so much with the formula but rather with the way it displays the total.

I'm looking for something like this (I zeroed out E5-E11 by hand): The balance should display "P0.00", or be blank, when the line is empty.

• I don't really understand what the formula is supposed to calculate. Your function subtracts the value in the Debit column, but the result in the Balance column has added it. How should it be? Feb 28 '15 at 14:03
• The "Balance" field at the end of each row is supposed to display the new balance after the debit/credit has been added to it. I'll edit my post to show what I'm trying to get. I do need to change that screenshot though, there was an error in the formula used in that shot, and I've since changed it. Feb 28 '15 at 14:39

I'd use this formula in `E3` and copy down:

``````=IF(AND(E2<>"",OR(C3<>"",D3<>"")),E2-C3+D3,"")
``````

EXPLANATION:
The `IF` statement contains 3 comma separated parameters:

1. logical_expression
2. value_if_true
3. value_if_false

In this case the logical expression is `AND(E2<>"",OR(C3<>"",D3<>""))`. `AND` takes multiple logical expressions as comma separated parameters, and returns `TRUE` only if all expressions are true. In this case these two:

1. `E2<>""`
2. `OR(C3<>"",D3<>"")`

Number one checks if there is anything in the cell above (if it's not equal to nothing). Number two is an `OR` function, works as `AND` but returns `TRUE` if only one or more of the expressions are true. In this case:

1. `C3` (the Debit value on this row) is not empty
2. `D3` (the Credit value on this row) is not empty

So to summarize If the cell above has a value, and `Debit` or `Credit` has a value. The `value_if_true` is used. In the example: `E2-C3+D3` (the cell above minus `Debit` plus `Credit`).

If the expression evaluates to false, the `value_if_false` is used, in this case `""` (an empty string).

• That worked great, thanks a bunch! Mar 1 '15 at 0:30