checkbook register

johnjo · May 23rd, 2002, 08:44 PM

I'm in the process of creating a checkbook register for my SOHO (Small Office/Home Office) business. I have the formating done but it's the formula that's really a pain. Here's the break down:
There's three columns-"Debit","Credit"&"Balance". In the first balance cell,which is H17, I have this formula: =sum(-F17+G17)+beginning_balance. F17 is the first debit cell, G17 is the first credit cell & beginning_balance is a single cell which I enter the beginning balance or balance brought forward value. In the cells beneath the first balance cell I have this formula: =sum(-F18+G18)+H17 going on down respectivley. Here's my problem: I want to have a running total formula in the balance cells that well not show the balance straigh down the column, I want it give a single running total when a value is either debit or credit. I also want the subsequent cells to be blank. Can someone help. Thank you in advance.

Rocky E · May 23rd, 2002, 08:59 PM

Hi John,

How about =IF(F18+G18>0,SUM(-F18+G18)+H17,"")

Yogi Anand · May 23rd, 2002, 09:14 PM

Hi Johnjo:
Using your desired setup, I have setup the following worksheet simulation simulation ...

Microsoft Excel - y020523.xls
H15		=	100
	E	F	G	H	I
14
15				100
16		Debit	Credit	Balance
17				:alert('=SUM(-F17+G17+beginning_balance)')>100
18		20	50	:alert('=IF(AND(F18="",G18=""),"",-F18+G18+H17)')>130
19		1200	1500	:alert('=IF(AND(F19="",G19=""),"",-F19+G19+H18)')>430
20			150	:alert('=IF(AND(F20="",G20=""),"",-F20+G20+H19)')>580
21		320		:alert('=IF(AND(F21="",G21=""),"",-F21+G21+H20)')>260
22
23
Sheet9

Click on a hyperlinked cell to see the underlying formula

The formula used in the Balance cell is:

=IF(AND(F18="",G18=""),"",-F18+G18+H17)

Regards!

johnjo · May 31st, 2002, 09:01 PM

Thanks, everyone for your help. Rocky, your suggestion was really good. It did the job. Again, thanks everyone for the prompt response and help.

Johnjo

May 23rd, 2002, 08:59 PM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Hi John, How about =IF(F18+G18>0,SUM(-F18+G18)+H17,"") __________________ Hope this helps! Rocky <h6>"Be not the first by whom the New are try'd, Nor yet the last to lay the Old aside." Alexander Pope (1688-1744).</h6>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 08:44 PM	#1
johnjo New Member Join Date: May 2002 Posts: 7	I'm in the process of creating a checkbook register for my SOHO (Small Office/Home Office) business. I have the formating done but it's the formula that's really a pain. Here's the break down: There's three columns-"Debit","Credit"&"Balance". In the first balance cell,which is H17, I have this formula: =sum(-F17+G17)+beginning_balance. F17 is the first debit cell, G17 is the first credit cell & beginning_balance is a single cell which I enter the beginning balance or balance brought forward value. In the cells beneath the first balance cell I have this formula: =sum(-F18+G18)+H17 going on down respectivley. Here's my problem: I want to have a running total formula in the balance cells that well not show the balance straigh down the column, I want it give a single running total when a value is either debit or credit. I also want the subsequent cells to be blank. Can someone help. Thank you in advance.

May 31st, 2002, 09:01 PM	#4
johnjo New Member Join Date: May 2002 Posts: 7	Thanks, everyone for your help. Rocky, your suggestion was really good. It did the job. Again, thanks everyone for the prompt response and help. Johnjo