Thread: Excel checkbook with running Balance - leave blank balance on blank rows

1. Excel checkbook with running Balance - leave blank balance on blank rows

I have an Excel Checkbook with withdrawals, deposits, and a running balance.

I need a formula that will update the balance if I enter either a Withdrawal OR a Deposit. Currently, it only updates if I enter a Withdrawal. I have Withdrawals and Deposits and need to return 0 if both are empty, while returning a balance if either has an entry. Withdrawals and Deposits are on the same row.

The current formula is =IF(H7="","",K6+J7-H7) and the balance works if I enter an amount in column H (withdrawal), but if I enter anything in J (deposit), K (the balance) stays blank.

Column H is the withdrawal, J is Deposit, K is the balance.

Thanks for any help.

eramsell

2. =IF(COUNT(H7,J7)=0,"",K6+If(J7="",0,J7)-IF(H7="",0,H7))
should work.

3. Alternatively:
=IF(AND(H7="",J7=""),"",K6+J7-H7)

4. Yes - if the cells are empty (rather than containing a formula that returns "" for example) that will also work.

5. Actually that'd work for a formula returning "". It wouldn't work if any other text was entered in the cell, as then you'd be trying to sum text values, whereas your formula only counts if there's a number in the cell and avoids that.

I never said mine was a better solution, just a valid alternative :-)

6. It would only work if both contained "" since it wouldn't attempt the second part. If only H7 or K7 had "" in and the other had a value in, you would still be doing arithmetic on text. That was my point.
I suspect the cells are actually blank here so the issue should be moot, but I thought I'd mention it just in case. It's not a competition!

7. to Rory

Originally Posted by rory
=IF(COUNT(H7,J7)=0,"",K6+If(J7="",0,J7)-IF(H7="",0,H7))
should work.
I tried this and it didn't return anything. No errors, but no value either. I have Excel 2007. Does that make a difference?

8. Originally Posted by Kafrin
Alternatively:
=IF(AND(H7="",J7=""),"",K6+J7-H7)
Kafrin- I also tried this formula, and this also didn't work. It also returned nothing, with no errors either.

9. Both formulas would work based on the formula you posted. Please double check what you posted and make sure you have calculation set to automatic.

10. Here is a screenshot of kafrin's formula as entered in the cell.

I checked, and doublechecked; I can't see anything different

Excel Checkbook.jpg

11. Surely that should be in K7?

12. See attached demo.

13. Ah, the fix was so simple. I changed the cell references to row 4 instead of 7.

It works now.

I really appreciate this!

14. Just curious - In an "Excel checkbook with running Balance", if the balance in Cell K3 is 364.7 and deposits and withdrawals in row 4 are blank, should "Balance" in K4 still be 364.7? Further, if row 5 shows a deposit of 25, what should cell K5 show as a Balance?

15. Originally Posted by tfspry
Just curious - In an "Excel checkbook with running Balance", if the balance in Cell K3 is 364.7 and deposits and withdrawals in row 4 are blank, should "Balance" in K4 still be 364.7? Further, if row 5 shows a deposit of 25, what should cell K5 show as a Balance?
I don't want a running balance until I enter an amount on line 4. There wouldn't be any point in putting the current balance on a blank line. I suppose you could, but there is no need to.

In response to the second question, I suppose it would be based on the last balance. I haven't tried it.

My mistake in not getting it to work was caused by the fact that I had made the formula earlier before I removed some merged rows. That changed the cell row, messing me up.

Anyway, all's well. I'll probably be asking some other questions later. I'll leave be for now.

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•