Credit Debit running balance with blank rows

E

Eskonn

New Member
Joined
Mar 28, 2018
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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]
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Try this formula in H6 copied down
=IF(OR(ISNUMBER(E5),ISNUMBER(G5)),H$3-SUM(E$5:E5)+SUM(G$5:G5),"")

M.
 
Last edited:
Upvote 0
Hi,

Here's another way:


Book1
EFGH
1DebitCreditBalance
2
31849.53
4
525.98 
61823.55
752
81771.55
930
101801.55
1149
121752.55
1318
141770.55
Sheet19
Cell Formulas
RangeFormula
H5=IF(ISODD(ROW(A5)),"",H$3-SUM(E$5:E5)+SUM(G$5:G5))


H5 formula copied down.
 
Upvote 0
Another option for H6, copied down.

Excel Workbook
EFGH
1CHECK 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.
 
Last edited:
Upvote 0
jtakw said:
Hi,

Here's another way:

EFGH
DebitCreditBalance

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1849.53[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25.98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1823.55[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1771.55[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1801.55[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1752.55[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1770.55[/TD]

</tbody>
Sheet19​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF(ISODD(ROW(A5)),"",H$3-SUM(E$5:E5)+SUM(G$5:G5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



H5 formula copied down.
Click to expand...


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
 
Upvote 0
Peter_SSs said:
Another option for H6, copied down.

Balance

EFGH
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
CellFormula
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.
Click to expand...


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
 
Upvote 0
Eskonn said:
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
Click to expand...

The error is happening at the end of the formula. It works through the -E5 but then the error results after the +G5,""
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
186
Omran Y
O
A
  • Solved
How merging by sum DEBIT,CREDIT columns for each name
2 3
Replies
27
Views
305
Abdo
A
A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
192
Alaa mg
A
A
  • Question Question
populate balance for the last row based on sheet name from file to another
Replies
0
Views
239
Alaa mg
A
Jeffrey Mahoney
  • Solved
Get Items From Descriptions Based on List
Replies
5
Views
401
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top