View Full Version : Excel Formula for Cashbook entries

20-07-2009, 05:55 PM
Can someone please help me with setting up an excel formula that will allow me to keep a running record in a cashbook type excel worksheet? I want three columns operating eg Deposit, Withdraw and Balance, set up so that excel automatically calculates the running balance as I enter either a deposit or make a withdraw on the row below.

Thanks in anticipation.

20-07-2009, 07:13 PM
Assuming the following:

Column A is Deposit
Column B is Withdrawals
Column C is the running balance

In cell C2, type =SUM($A$1:A1)-SUM($B$1:B1)

Copy down as need be.

20-07-2009, 07:56 PM
Thanks Bogan. Have tried this formula but it doesn't seem to work:) I am using Excel 2007 not sure if that creates a problem?

20-07-2009, 08:01 PM
Shouldn't do.

How are you copying it? Are you selecting the bottom right corner of the cell and dragging?

Actually, I realised one big fault with that formula I gave. :horrified Sorry about that.

In cell C2, type =SUM($A$2:A2)-SUM($B$2:B2)

This assumes row 1 has a description of the columns.

Oh and both deposits and withdrawals are positive. If withdrawals are negative, use =SUM($A$2:A2)+SUM($B$2:B2)

20-07-2009, 08:33 PM
Ok will try this soon...thank's for your help which is much appreciated :)

20-07-2009, 08:46 PM
Thank me when you have it sorted. :xmouth: Good luck.

20-07-2009, 10:05 PM
I use =C4+A5-B5 in column C.
This assumes an actual amount in C4 as there has to be a starting balance somewhere.
Use fill down to populate column C as far as you need to.
Formula is saying the current balance is the previous balance plus any deposit minus any withdrawal.
You can format the cells as currency
As usual there are many different ways of doing the same thing ..

21-07-2009, 09:23 AM
Thanks Othesea......that looks good too. Much appreciated:)