+ Reply to Thread
Results 1 to 14 of 14

Running Balance

  1. #1
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Question Running Balance

    I am having trouble working out the formula for the attached.
    columns e to l need to be a minus total and column m is to be added. All to be added to the above total in column n but not to show anything if no entry.

    I have created a sum if formula but can't get it to work properly.
    Attached Files Attached Files

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    Maybe

    =IF(SUM(E6:L6)=0,N5,-SUM(E6:L6)+N5)
    Last edited by royUK; 04-21-2011 at 04:23 AM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,410

    Re: Running Balance

    The easiest way would be to have this formula in cell N6:

    =N5-SUM(E6:I6)+M6

    and drag down.

    You could use Conditional Formatting to "hide" values that haven't changed, for example, the rows where there are no entries.

    Condition: =N6=N5 and format font as white (assuming the background is white/default). That's in cells N6 to N23, that is all the cells except the opening and closing balances.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Running Balance

    I know their is a formula to hide formulas if nothing is equalling it. More advanced than using conditional formatting. Thanks, but no thanks.

  5. #5
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Running Balance

    Thanks but I also need it to not show any formula if nothing is entered.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    There's no way to tell who your remarks are aimed at.

    If you don't have a value in M then you would generate an error, without the formula then M would be empty. Try this and see the problem with the Reimburse entry

    =IF(SUM(E7:L7)=0,"",-SUM(E7:L7)+N6)

  7. #7
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Running Balance

    Sorry Roy but I did find you helpful. That formula however won't work at all. :-)

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    Try the amended formula, I've just noticed that part didn't copy properly

  9. #9
    Registered User
    Join Date
    04-03-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Running Balance

    Roy, got it. You were so so close. Many many thanks. Awesome help.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    You probably need to add the reimbursement column

    =IF(SUM(E6:L6)=0,N5,-SUM(E6:L6)+N5)+M6

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,410

    Re: Running Balance

    Thanks for the feedback. I was taught a long time ago that "feedback is a gift that you can choose to accept or reject". On this occasion, I think I'll pass ;-)

    To give negative rep to someone who has only tried to help you seems a bit harsh:

    I feel you need to learn alot more about Excel before you can start helping people. I actually know more than you.
    This seems a somewhat subjective statement and I would have more confidence in its validity if there were some evidence of your knowledge elsewhere in the forum ... but there isn't, as far as I can see.

    I wish you well and hope you get answers to all your questions, issues and problems. Rest assured that none of them will come from me ... or, at least, you won't see any of my pitiful attempts at help.

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    Sandyshirl, anyone can offer help to the best of their ability. Giving negative rep to someone trying to help with such comments is uncalled for

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,410

    Re: Running Balance

    @RoyUK: thank you. TMS

  14. #14
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Running Balance

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1