+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    03-17-2017
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    9

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

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-17-2017
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    9

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    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: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-17-2017
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    9

    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. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    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. #7
    Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    292

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

    Is my approach any help ?
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    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: copy to clipboard
    Please Login or Register  to view this content.
    In I5
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In I6
    Formula: copy to clipboard
    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
    Dave

  9. #9
    Registered User
    Join Date
    03-17-2017
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    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. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,936

    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. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,875

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Carryover End of Month Balance at Beginning of Each Month
    By cjrube3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-01-2016, 05:04 PM
  2. How to sum data by month, by quarter, by year?
    By kevinSHR in forum Excel General
    Replies: 3
    Last Post: 05-06-2016, 04:06 PM
  3. [SOLVED] Sum totals from beginning of last year to the end of last month last year
    By rs1aj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2015, 02:20 PM
  4. [SOLVED] YTD total from beginning of year to last month
    By rs1aj in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-05-2015, 06:16 PM
  5. Find out last week, last month, last quarter from todays date
    By meus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2015, 01:33 AM
  6. Calculate # of sales per week, month & quarter
    By AlcatrazT2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 06:25 PM
  7. Expanding 4th quarter by month ONLY for certain year
    By Melvinrobb in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-28-2013, 06:13 PM

Bookmarks

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