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

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.