PDA

View Full Version : Excel Formula for Cashbook entries



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

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

smurf
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?

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

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

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

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

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