Results 1 to 3 of 3

Thread: How can I return the most recent account balance from a chart of multiple accounts
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How can I return the most recent account balance from a chart of multiple accounts

    Hi everyone,

    I thought this issue would be simple but it's actually proving to be quite difficult.


    I need to write a formula that will return the last balance for an account within a table of multiple accounts.
    Essentially the formula needs to look at the MAX date for all transactions impacting one account within my chart of accounts. Then look at it's ending running total balance and return it.



    I've tried using the following formula:

    Code:
    =INDEX(Master[Validation],MATCH(MAXIFS(Master[Transaction Date],Master[Account '#],A31),Master[Transaction Date],0))
    Where;

    Master = The name of my master table
    Validation = the running total column - where the figure I'd like to return resides
    Transaction Date = Transaction Date - where the maximum date needs to be pulled from
    Account '# = Account Number - the criteria used to distinguish which validation amount should be taken.
    A31 (Column A) = the list of accounts that make up the criteria to be searched for within my master.

    The issue with this current formula is that it will look up the max date for an account but then return the first balance in the validation column that matches the date.

    So even though the max date being picked up from the Nested MAXIFs function is correct. Any validation balance that matches that date can be returned, when I need it to only return a balance that matches the account number in question.



    I've been wracking my brain on this one trying to use various forms of Array, Index and Lookup functions. So I'd appreciate any assistance one could provide.

    Thank you.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,912
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How can I return the most recent account balance from a chart of multiple accounts

    One way

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Transaction Date Account Validation
    2
    20/08/2019
    AC1
    773.04
    AC1
    681.01
    {=INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))}
    3
    22/08/2019
    AC1
    959.70
    AC2
    240.09
    4
    23/08/2019
    AC1
    985.63
    AC3
    626.02
    5
    29/08/2019
    AC1
    316.05
    6
    31/08/2019
    AC1
    263.46
    7
    07/09/2019
    AC1
    681.01
    8
    23/08/2019
    AC2
    207.78
    9
    24/08/2019
    AC2
    971.50
    10
    26/08/2019
    AC2
    430.09
    11
    27/08/2019
    AC2
    459.85
    12
    01/09/2019
    AC2
    107.55
    13
    03/09/2019
    AC2
    240.09
    14
    24/08/2019
    AC3
    210.40
    15
    26/08/2019
    AC3
    429.55
    16
    27/08/2019
    AC3
    285.10
    17
    31/08/2019
    AC3
    711.98
    18
    02/09/2019
    AC3
    589.78
    19
    04/09/2019
    AC3
    779.40
    20
    06/09/2019
    AC3
    626.02
    Sheet: Sheet1

    ARRAY FORMULA in G2 - must be committed with {CTRL}{SHIFT}{ENTER}
    =INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))

    Entered as array is automatically enclosed in { }
    {=INDEX(Master[Validation],MATCH(F2&MAXIFS(Master[Transaction Date],Master[Account],F2),Master[Account]&Master[Transaction Date],0))}

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,932
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How can I return the most recent account balance from a chart of multiple accounts

    Or,

    In G2, regular formula copied down :

    =LOOKUP(9^9,Master[Validation]/(Master[Account]=F2)/(Master[Transaction Date]<>""))

    Reagrds

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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