+ Reply to Thread
Results 1 to 5 of 5

Maintain correct Balance when sorting differently.

  1. #1
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    130

    Maintain correct Balance when sorting differently.

    Dear Forum.
    Looking for assistance with the following…
    Basically looking to have a very basic bookkeeping workbook. Please see attached dummy sample similar to what I have configured.
    What I would like however is to be able to sort by various headers but for the balance column (F) to maintain what would have been the correct balance for that date. Currently for all but the first row I have a basic formula for the “Balance” (column F), that looks to the row above and adds/subtracts the transaction amount for that row. The first date/row has a different formula such that it is looking at the opening balance (F10) and adding/subtracting the first transaction. This all works fine so long as I don’t sort differently. In order to be able to sort by something other than date and maintain the correct balance for that date, it would require a more intelligent formula such that it is looking at the balance of the previous date. Any ideas on a formula that would accomplish that for column F?
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,679

    Re: Maintain correct Balance when sorting differently.

    In F13 try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As your data are in Excel table, the formula will be automatically copied down.

    The only disadvantage I see (at first glance) is that it gives balance "at the end of the day" so if you have a couple of rows for the same date then the balance in each of them will be the same (as for the last row with that date).
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Maintain correct Balance when sorting differently.

    That worked great Kaper. The issue regarding the multiple transactions on same day was a consideration I hadn't thought about. I did want it such that it would make mathematical sense for each transaction so I have now added a time column and another column to combine the date and time. I then modified your formula such that it looks up the combined date and time column for determining the balance. Works a treat.
    Thank you very much!!!

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    6,679

    Re: Maintain correct Balance when sorting differently.

    You are welcome!
    And thanks for sharing your solution for date duplicates, as well as for marking thread solved and for rep+

  5. #5
    Forum Contributor
    Join Date
    04-27-2010
    Location
    Kinsale, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Maintain correct Balance when sorting differently.

    Very welcome for the rep+

    I guess if I had thought more about it at the time, another way to overcome the duplicate date entries would have been to have a column to the very left with a simple number for each row. Then have the formula lookup the balance in the row number before that number to add/subtract the pay in/out (hindsight is 20:20 )

    Thank you again for your help. Have put some tweaks on my actual workbook and it's shaping up really nice (by my standards anyway )

+ 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