Excel formulas

  gudda96 18:54 29 Apr 03
Locked

My friend is looking for forulas or solution to use in his banking spreadsheet.

he runs 3 columns credits/debits/balance and although we know the main formulas, he simply wants to enter cheque amount from his counterfoil and as he does, he wants banance column to change.

And the same when he enters an amount from his pay-in book.

he wants it to be similar to the bank statement which is a running total.

So any Excel experts, please divulge.

  VoG™ 19:00 29 Apr 03

Without knowing exactly what is in which column it's a bit difficult.

Let's say they have positive or negative amounts going into column C, with an initial balance in C1. In D1 enter the formula

=SUM($C$1:$C1)

Hover the cursor over the bottom right of cell D1 and the pointer will turn into a +

Click and hold down the left mouse button and pull the mouse down to copy the formula down as far as you want.

If you then look in D2 the formula will be =SUM($C$1:$C2), in D5 it will be =SUM($C$1:$C5) and so on; i.e. a running total.

  short-circuit 20:45 29 Apr 03

I notice you say that your friend has three columns to represent his calculations and VoG™ has put forward an excellent two column solution. It will rely on your friend remembering to put a - sign before any of his debit amounts. If you want to run a three column solution with all credits in column C and debits in column D and balance in the column E and a starting balance in cell E2, then the formula to type into E3 is

=E2+C3-D3

For this solution Row 2 would represent your starting balance and Row 3 would be where your friend would add the first transaction.
Copy this formula down the column as VoG™ suggested and it should look pretty much like your bank statement.

Because the - sign is included in the formula, your friend will not need to remember to put a - before each of his debits.

If you want the Balance column to stay blank until you have entered either a debit or a credit, post again and I'll add an if clause to that formula for you.

Scott.

  tbh72 22:32 29 Apr 03

You can also use an IF statement to limit the visibility of figure's running down the page prior to any debit's or crdit's being entered.

  gudda96 07:58 30 Apr 03

Short Circuit--Vog--TBH72

Thank you all for your quick response, I have passed your answers on and will post"Resolved" when I hear back from him.

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now