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:
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.
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.
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.
