Credit Debit running balance with blank rows

[Eskonn]

I am looking for a formula to keep a running balance that accounts for every other row being blank.
The Balance starting cell is H3
The starting row for the credits and debits is ROW 5
I would like to keep the running balance on the even ROWS with the first cell being ROW H6 and then every other ROW H8, H10, H12 etc.
In the example below Check Amount (Column E), Deposit (Column G), Balance (Column H)



[TABLE="width: 604"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]CHECK NO.[/TD]
[TD]DATE[/TD]
[TD]DESCRIPTION OF TRANSACTION[/TD]
[TD]REC. NO.[/TD]
[TD]CHECK AMOUNT (-)[/TD]
[TD] [/TD]
[TD]DEPOSIT (+)[/TD]
[TD]BALANCE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"][/TD]
[TD](H3)$1,849.53[/TD]
[/TR]
[TR]
[TD="colspan: 4"][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]3/12/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"](E5)$25.98[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] (H6) [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"](E7)$52.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] (H8)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"](G9)$30.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] (H10)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"](E11)$49.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] (H12) [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"](G13)$18.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] (H14)[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

I tried this as well but I have not been able to get it to work. When I use this formula the cell is blank. I am not getting an error in the cell just no data. Thanks

You may not be entering the formula at the correct row, you need to enter the formula at your First Debit/Credit row, in my sample, it's row H5, and obviously, you'll need to adjust the SUM ranges, do Not change the A5 cell reference in the IF test, as your OP stated, your debits/credits are on ODD rows, and you want the running Balance on Even rows. If you've reversed this (i.e., Debits and Credits on Even rows, and Balances on Odd rows), then change the A5 to A6 in the IF test of the formula.

 

[Eskonn]

You may not be entering the formula at the correct row, you need to enter the formula at your First Debit/Credit row, in my sample, it's row H5, and obviously, you'll need to adjust the SUM ranges, do Not change the A5 cell reference in the IF test, as your OP stated, your debits/credits are on ODD rows, and you want the running Balance on Even rows. If you've reversed this (i.e., Debits and Credits on Even rows, and Balances on Odd rows), then change the A5 to A6 in the IF test of the formula.

I did get it to work as well. Here is what it ended up being.
=IF(ISODD(ROW(A4)),"",H$3-SUM(E$5:E5)+SUM(G$5:G5))

This formula does have the balance in all the rows down the sheet. But it does work properly as credits and debits are added.
Thanks for your time

 

[Peter_SSs]

Peter -
Thanks for the suggestion. I tried this and I am getting and error #VALUE !

Sounds like you might have null strings (from formulas?) or space characters in columns E &/or G. You could try this modification

Excel Workbook
	E	F	G	H
1	CHECK AMOUNT (-)		DEPOSIT (+)	BALANCE
2
3				$1,849.53
4
5	$25.98
6				$1,823.55
7	$52.00
8				$1,771.55
9			$30.00
10				$1,801.55
11	$49.00
12				$1,752.55
13			$18.00
14				$1,770.55
15
16
Balance

 

[Eskonn]

Sounds like you might have null strings (from formulas?) or space characters in columns E &/or G. You could try this modification

Balance

	E	F	G	H
CHECK AMOUNT (-)		DEPOSIT (+)	BALANCE

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:142px;"><col style="width:17px;"><col style="width:96px;"><col style="width:101px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]$1,849.53 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]$25.98 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]$1,823.55 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]$52.00 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]$1,771.55 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]$30.00 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]$1,801.55 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]$49.00 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]$1,752.55 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]$18.00 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: right"]$1,770.55 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H6 =IF(COUNT(E5,G5),LOOKUP(9.99E+307,H$3:H5)-N(E5)+N(G5),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter -
That modification did work.
Thanks again for your suggestion and time.

 

[Peter_SSs]

Peter -
That modification did work.
Thanks again for your suggestion and time.

No problem.