1

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.

2
  • 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
1

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.