# Locating beginning and end of week, month, quarter or year balance

1. ## Locating beginning and end of week, month, quarter or year balance

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!!!

2. ## Re: Locating beginning and end of week, month, quarter or year balance

it is not efficient to calculate all column if one can define a range (done in the file)
closest dates with function min and max

3. ## Re: Locating beginning and end of week, month, quarter or year balance

Thanks tim201110

The ending balance works great. But the beginning balance needs to reflect balance in the row above. Which in the case of the sheet you posted would be \$22,025.78.

Thanks again for how you've helped!!!!!!

4. ## Re: Locating beginning and end of week, month, quarter or year balance

Hello ehollifield and Welcome to Excel Forum,
To get the beginning balance from 'the row above' modify the array entered formula in J4 (Tim's file) so that it reads:
Formula:
`Please Login or Register  to view this content.`
Let us know if you have any questions.

5. ## Re: Locating beginning and end of week, month, quarter or year balance

Thanks JeteMc!
I actually came up with something over the weekend that works better for me. I'm really unfamiliar with array formulas so the trouble I'm having is making new budget reports for different clients. I've got a few different budget reports for different people I'm working with. Tim's formula worked fine in the sample sheet I made up but after I tried using it in different sheets I begin getting REF# errors with it. Is there a formula that would work for finding the ending balance even if there wasn't a transaction on last date of the time period and also more that one transaction on that same last transaction date?

6. ## Re: Locating beginning and end of week, month, quarter or year balance

I am not able to replicate the problems.
Using the file attached to post #2 I put the date 3/31/2017 (a date not found column A on the 'Fund' sheet) in Report!D4 and still got the expected value in J4.
The date in D5 was already a date not being used (6/30/2017) and the expected result displays.
I changed the date the date in D5 to 6/29/2017 which has two transactions and get the expected value.
As to the #REF errors my initial thought is that the named ranges (date for column A and bal for column G ) might be missing or referenced to the wrong column.
If the issues persist it would be better to upload a desensitized sample of the spreadsheet(s) that cause them.

7. ## Re: Locating beginning and end of week, month, quarter or year balance

Is my approach any help ?

8. ## Re: Locating beginning and end of week, month, quarter or year balance

If I interpret the instructions and in file comments correctly this should work.

As long as the dates are in sequential order ascending there should be no reason not to reference whole columns if you use approximate lookup type.

In I4
Formula:
`Please Login or Register  to view this content.`
In I5
Formula:
`Please Login or Register  to view this content.`
In I6
Formula:
`Please Login or Register  to view this content.`

 C D E F G H I J 1 2 3 4 Start date 4/1/2017 Beginning Balance \$22,025.78 finds the date but I want it to report the amout prior to the deduction or deposit 5 End date 6/30/2017 Ending Balance \$37,409.07 finds closest to date but only top row 6 \$60,733.39 finds exact date but look for most bottom row

9. ## Re: Locating beginning and end of week, month, quarter or year balance

I really appreciate all the help I received with my problem. I wanted to close this out by showing the three formulas I chose to use. I keep all of the dates in descending order which allows these formulas to work.

To find the beginning balance of the selected time period I used.

=INDEX('Fund'!\$H:\$H,MATCH(INDEX('Fund'!\$A:\$A,MATCH(MIN(ABS('Fund'!\$A:\$A-\$F\$4)),ABS('Fund'!\$A:\$A-\$F\$4),0)),'Fund'!\$A:\$A,0))

To find the ending balance of the selected time period I used two formulas. The first one finds the closest date and the second one finds the bottom row of that date. I put the first formula off to the side of my report so it doesn't show up once it's printed.

=INDEX('Fund'!\$A:\$A, MATCH(\$F\$5,'Fund'!\$A:\$A,1))

=INDEX('Fund'!H:H,MAX((\$U\$5='Fund'!A:A)*MATCH(ROW('Fund'!A:A),ROW('Fund'!A:A))))

I'm not saying these formulas are the best way to solve my problem, but they are working for me for now...... I'm able to move them from different worksheets and apply them to similar scenarios.

Again, I really appreciate the help!!!

10. ## Re: Locating beginning and end of week, month, quarter or year balance

Appreciate the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

11. ## Re: Locating beginning and end of week, month, quarter or year balance

Glad you found something that does the job. Thank you for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1