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)

CHECK NO.	DATE	DESCRIPTION OF TRANSACTION	REC. NO.	CHECK AMOUNT (-)		DEPOSIT (+)	BALANCE
				(H3)$1,849.53
	3/12/2018			(E5)$25.98
							(H6)
				(E7)$52.00
							(H8)
						(G9)$30.00
							(H10)
				(E11)$49.00
							(H12)
						(G13)$18.00
							(H14)

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Try this formula in H6 copied down
=IF(OR(ISNUMBER(E5),ISNUMBER(G5)),H$3-SUM(E$5:E5)+SUM(G$5:G5),"")

M.

 

[jtakw]

Hi,

Here's another way:

Book1
	E	F	G	H
1	Debit		Credit	Balance
2
3				1849.53
4
5	25.98
6				1823.55
7	52
8				1771.55
9			30
10				1801.55
11	49
12				1752.55
13			18
14				1770.55
Sheet19

Cell Formulas
Range	Formula
H5	=IF(ISODD(ROW(A5)),"",H$3-SUM(E$5:E5)+SUM(G$5:G5))

H5 formula copied down.

 

[Eskonn]

That worked!
Thanks for the quick response.

 

[Eskonn]

Try this formula in H6 copied down
=IF(OR(ISNUMBER(E5),ISNUMBER(G5)),H$3-SUM(E$5:E5)+SUM(G$5:G5),"")

M.

That worked!

 

[Marcelo Branco]

Thanks for the feedback.

M.

 

[Peter_SSs]

Another option for H6, copied down.

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

A couple of other comments for your consideration:
- I would be a little wary of using the post #3 suggestion. If a user subsequently decides to add a new row at or near the top of the sheet, the formulas will return values in a different position to what you have requested.

- It may or may not be what you want but if copied down below your last debit/credit entry, jtakw's formula will continue to give a balance figure whereas Marcelo's formula & mine will only produce a balance as far down as the final debit/credit.

 

[Eskonn]

Hi,

Here's another way:

	E	F	G	H
1	Debit		Credit	Balance
2
3				1849.53
4
5	25.98
6				1823.55
7	52
8				1771.55
9			30
10				1801.55
11	49
12				1752.55
13			18
14				1770.55

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet19​

Worksheet Formulas Cell Formula H5 =IF(ISODD(ROW(A5)),"",H$3-SUM(E$5:E5)+SUM(G$5:G5)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

H5 formula copied down.

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

 

[Eskonn]

Another option for H6, copied down.

Balance

	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

<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>
</tbody>

Spreadsheet Formulas

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

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



A couple of other comments for your consideration:
- I would be a little wary of using the post #3 suggestion. If a user subsequently decides to add a new row at or near the top of the sheet, the formulas will return values in a different position to what you have requested.

- It may or may not be what you want but if copied down below your last debit/credit entry, jtakw's formula will continue to give a balance figure whereas Marcelo's formula & mine will only produce a balance as far down as the final debit/credit.

Peter -
Thanks for the suggestion. I tried this and I am getting and error #VALUE !
I am trying to see if I can find the error and will reply if I get it

Thanks for your time

 

[Eskonn]

Peter -
Thanks for the suggestion. I tried this and I am getting and error #VALUE !
I am trying to see if I can find the error and will reply if I get it

Thanks for your time

The error is happening at the end of the formula. It works through the -E5 but then the error results after the +G5,""