End of month financial report

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,874
Office Version
  1. 2010
Platform
  1. Windows
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.)

DateAccountCash DepositBank DepositCash WithdrawBank WithdrawTotalCash1Cash2AB BankShow
2020/04/01AB1,941,932194,873,0500096,196,2210
2020/04/05Show80,000,000114,873,0500000
2020/04/05AB80,000,000194,873,05000176,196,2210
2020/04/05AB8,021,918202,894,96800184,218,1390
2020/04/14Cash22,960,000199,934,968015,716,82900
2020/04/20Cash2588,000199,346,968015,128,82900
089,963,8503,548,00080,000,000199,346,96841,681,74615,128,829184,218,1390


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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top