I have developed a sheet to help someone track their budget and I need to locate the beginning and ending balance of which ever time period they select. They have the option of selecting date ranges of a month, a quarter or a year. I have included a sample of the sheet to help explain my problems. The running balance of their transactions are on the tab titled “Fund” and I want the beginning and ending balance of the time they select to be reflected on the tab titled “Report”. I have not started trying to figure out the formula to find the beginning balance yet because I thought it would be the easier of the two. But now I’m confused with both formulas I need. My first problem is the transaction/s may not have occurred on the beginning and/or ending date of the time period they have selected, so I need to find the closest date. The second problem is there may be multiple transactions near the beginning or ending of the time period, so I need to ensure the actual beginning and ending balances are accurate. For the beginning of the time period they select I need to show the dollar amount before the transactions of that time period adds or subtracts from the balance. Likewise for the ending of the time period they select I need to show the dollar amount after the transactions of that time period have added or subtracted to the balance. I have come up with the two formulas below but they only solve my problems one at a time. I don’t know how to fix both of my problems with one formula.
=INDEX('Fund'!G:G,MATCH(INDEX('Fund'!A:A,MATCH(MIN(ABS('Fund'!A:A-D5)),ABS('Fund'!A:A-D5),0)),'Fund'!A:A,0))
This formula, in cell I5, finds a date closest to the selected date but it only reflects the top row. If there is more than one transaction that is completed on the closest date to time period that was selected the correct ending balance will not be reported.
=INDEX('Fund'!G:G,MAX(($D$5='Fund'!A:A)*MATCH(ROW('Fund'!A:A),ROW('Fund'!A:A))))
This formula, in cell I6, finds the exact date but it will look at multiple transactions and select the one nearest to the bottom which results in the actual ending balance. But this formula doesn’t work if the last transaction/s is not on the last day of the time period that is selected.
Thanks in advance for the help!!!
Bookmarks