I have an Excel table. I can select a month (Column Date) to show the transactions in that month. What I'm looking for are the formulae to show the net value/balance of each account at the end of the selected month. The formulae should accommendate the fact that the selected month may change.
Total is the sum of all accounts. Its value is affected by every transaction.
Cash1 and Cash2 are cash accounts.
AB is a bank account. Only AB bank transactions will affect AB Bank's balance.
Show a trade show. It ends in April (withdrawl of 80,000,000). So, its balance becomes zero in April.
There are formulae to calculate the balance of each account if the account's balance is affected by a transaction. If an account is not affected by the transaction, the formula returns zero (may need to modify the formula to return blank in this case.)
Total should show the last non-zero value(*). I have a formula for it.
Cash1: there is no transaction in April. The report should show the last non-zero value, which is in March. So, cannot just look at the data in April.
Cash2: should show the last non-zero value(*).
AB Bank: should the last non-zero value(*).
Show: should show the balance, which is zero (after the withdrawl of 80,000,000).
(*) Showing the last non-zero value is convenient but actually incorrect, as seen in Show. Before, I had used showing last non-zero value for Show and it had worked well until the the month the account was closed. Then showing last non-zero value was no longer correct because the balance was now zero. The cell should now show zero. So, what is really needed is to show the balance at the end of the selected month. I know how to write the formula to show the last non-zero value but not the balance.
The formula to show the last non-zero value for Total is in the following. The actual data is between row 5 - 400.
Total is the sum of all accounts. Its value is affected by every transaction.
Cash1 and Cash2 are cash accounts.
AB is a bank account. Only AB bank transactions will affect AB Bank's balance.
Show a trade show. It ends in April (withdrawl of 80,000,000). So, its balance becomes zero in April.
There are formulae to calculate the balance of each account if the account's balance is affected by a transaction. If an account is not affected by the transaction, the formula returns zero (may need to modify the formula to return blank in this case.)
| Date | Account | Cash Deposit | Bank Deposit | Cash Withdraw | Bank Withdraw | Total | Cash1 | Cash2 | AB Bank | Show |
| 2020/04/01 | AB | 1,941,932 | 194,873,050 | 0 | 0 | 96,196,221 | 0 | |||
| 2020/04/05 | Show | 80,000,000 | 114,873,050 | 0 | 0 | 0 | 0 | |||
| 2020/04/05 | AB | 80,000,000 | 194,873,050 | 0 | 0 | 176,196,221 | 0 | |||
| 2020/04/05 | AB | 8,021,918 | 202,894,968 | 0 | 0 | 184,218,139 | 0 | |||
| 2020/04/14 | Cash2 | 2,960,000 | 199,934,968 | 0 | 15,716,829 | 0 | 0 | |||
| 2020/04/20 | Cash2 | 588,000 | 199,346,968 | 0 | 15,128,829 | 0 | 0 | |||
| 0 | 89,963,850 | 3,548,000 | 80,000,000 | 199,346,968 | 41,681,746 | 15,128,829 | 184,218,139 | 0 | ||
Total should show the last non-zero value(*). I have a formula for it.
Cash1: there is no transaction in April. The report should show the last non-zero value, which is in March. So, cannot just look at the data in April.
Cash2: should show the last non-zero value(*).
AB Bank: should the last non-zero value(*).
Show: should show the balance, which is zero (after the withdrawl of 80,000,000).
(*) Showing the last non-zero value is convenient but actually incorrect, as seen in Show. Before, I had used showing last non-zero value for Show and it had worked well until the the month the account was closed. Then showing last non-zero value was no longer correct because the balance was now zero. The cell should now show zero. So, what is really needed is to show the balance at the end of the selected month. I know how to write the formula to show the last non-zero value but not the balance.
The formula to show the last non-zero value for Total is in the following. The actual data is between row 5 - 400.
Excel Formula:
=IF(SUM(INDIRECT("G5:G"&AGGREGATE(14,7,(ROW(A5:A400))/SUBTOTAL(2,OFFSET(A5,ROW(A5:A400)-ROW(A5),0)),1)))=0,0,LOOKUP(2,1/INDIRECT("G5:G" & AGGREGATE(14,7,(ROW(A5:A400))/SUBTOTAL(2,OFFSET(A5,ROW(A5:A400)-ROW(A5),0)),1) ),INDIRECT("G5:G" & AGGREGATE(14,7,(ROW(A5:A400))/SUBTOTAL(2,OFFSET(A5,ROW(A5:A400)-ROW(A5),0)),1) )))
