# End of month financial report

#### yky

##### Well-known Member
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.)

 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) )))``

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Replies
0
Views
236
Replies
2
Views
225
Replies
0
Views
299
Replies
0
Views
213
Replies
2
Views
135

1,136,969
Messages
5,678,893
Members
419,787
Latest member
juanam

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back