Check Book Calculations

[Richard]

I'm wanting to keep track of all my checkbook transations
on excel. Can someone help me to get the correct formula.
Example: Column E Debit(-), Column F Credit (+), Column G
Balance. This works fine untill I have more Debits than
Credits =G1-E2+F2, formula starts in G2
Thanks in advance!

 

[Paul B]

Richard, with you starting balance in G2, put this in G3 and copy down
=(IF(OR(E3,F3>0),G2-E3+F3,""))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[David McRitchie]

Hi Joe,
Please do not post attachments in text newsgroups. They do not
make it into the Usenet archives -- Google Groups. . Nice layout.
But you do not want to use merged cells, borders would work
almost as well.

There are some problems with your method

The formula
F5: =IF(AND(ISBLANK(D5),ISBLANK(E5)),"",F4-D5+E5)

should be rewritten as
F5: =IF(AND(ISBLANK(D5),ISBLANK(E5)),"",OFFSET(F5,-1,0)-D5+E5)

Otherwise you will not be able to insert and delete rows without having
lots of problems (i.e. #REF! errors).

The Color Formatting should be done by Conditional Formatting rather
than cell pattern formatting. Otherwise sorting, inserting, deleting will
cause you problems there as well.

Remove all formatting and start over, use borders instead of merged cells.

Select columns A:F with cell A3 as the active cell (due to merged cells)
Format, Conditional Formatting,
Formula 1: is =AND(ROW(}>4,MOD(ROW(),2))

You can obtain free web space at Geocities.com if you want to
store an Excel workbook. It will last only as long as you maintain your
site. But the archives will not pick up attachments at all.

Use normal cell formatting for RED numbers showing negative balances.

One further note the use of Dr(-) and Cr(+) is as printed in most US checkbooks,
are used incorrectly -- unless you really think you should be keeping
records for the bank rather than for yourself. In other words if you
were keeping your own records for yourself Dr(+) Cr(-}. Just keep
this in mind if you ever take an accounting class.

More information on why you should be using OFFSET for your formulas:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#offset