+ Reply to Thread
Results 1 to 3 of 3

Showing Account Balances Nearest to the End of a Month

  1. #1
    Registered User
    Join Date
    05-29-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Showing Account Balances Nearest to the End of a Month

    Hello I'm trying to create a pivot table that displays the balance of an account

    The table containing the data will look something like this:

    Date.................Account 1..........Account 2
    04 May 2010..........200...............435
    05 May 2010.............................435345
    07 May 2010..........432.........................
    08 May 2010..........43534..........4534
    09 May 2010.................................345
    12 June 2010...........43114
    16 June 2010..........3453................534
    19 June 2010
    21 June 2010..........6343.......... 54


    The pivot table will have the dates horizontally across the page (columns). There will be two rows with the account balances.

    Now, when I group the dates by month I would like to see the balance of the account, which is closest to the end of the month (not the sum or any other function Excel provides in the value field settings. How do I do this? Any help is much appreciated!!

    This is what the pivot table should look like

    Date....................May 2010..........June 2010
    Account 1..............43534................6343
    Account 2................345................. 54
    Last edited by NBVC; 06-05-2010 at 07:38 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Showing Account Balances Nearest to the End of a Month

    I think to do this, you will need to add a couple of helper columns to the database..

    Assuming your data is in A1:C10 (including headers), then add 2 new headers to match the Account numbers... and in D2 enter formula:

    Please Login or Register  to view this content.
    copied to next column and down.

    Then you can use those 2 columns to base your Pivot table on....


    See attached.
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-29-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Showing Account Balances Nearest to the End of a Month

    wow that's very helpful, thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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