Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    =IF(COUNT(H7,J7)=0,"",K6+If(J7="",0,J7)-IF(H7="",0,H7))
    should work.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Jun 2011
    Location
    Dorset, UK
    Posts
    6
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Alternatively:
    =IF(AND(H7="",J7=""),"",K6+J7-H7)
    K

    Software-Matters ~ Straightforward Solutions to Take Care of Your Business

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes - if the cells are empty (rather than containing a formula that returns "" for example) that will also work.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Jun 2011
    Location
    Dorset, UK
    Posts
    6
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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 :-)
    K

    Software-Matters ~ Straightforward Solutions to Take Care of Your Business

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    to Rory

    Quote Originally Posted by rory View Post
    =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?
    Last edited by eramsell; 2011-06-28 at 23:59.
    eramsell

  8. #8
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Kafrin View Post
    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.
    eramsell

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    eramsell

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Surely that should be in K7?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    See attached demo.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah, the fix was so simple. I changed the cell references to row 4 instead of 7.

    It works now.

    I really appreciate this!
    eramsell

  14. #14
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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. #15
    New Lounger
    Join Date
    Jun 2011
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tfspry View Post
    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.
    Last edited by eramsell; 2011-07-01 at 00:34. Reason: Added answer
    eramsell

Page 1 of 2 12 LastLast

Posting Permissions

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