Google Sheets – How to Get a Running Balance

google sheets

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

enter image description here

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):
enter image description here

The balance should display "P0.00", or be blank, when the line is empty.

Best Answer

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).

Related Topic