+ Reply to Thread
Results 1 to 2 of 2

Determine monthly volume from weekly figures where weeks cross over at month end

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    378

    Determine monthly volume from weekly figures where weeks cross over at month end

    Good morning

    I need to determine a monthly total on a worksheet where the figures are provided weekly. The challenfge is that some weeks do not start or end at the beginning / end of the month, so only portions form those weeks would be counted into each month.

    For the purposes of this sheet, all days are assumed equal (ie the weekly total / 7 = daily figure). There is no weighting for weekends etc.

    Please see attached example.Monthly total Sum.xlsx
    Last edited by Grimace; 08-22-2012 at 09:07 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,140

    Re: Determine monthly volume from weekly figures where weeks cross over at month end

    I've deleted your column G, so that all your dates are contiguous, and so the first full week of a month also has the part-total from the previous week added in, and the final week of a month shows the total just for that month. As the formula looks at both the previous week's commencing date and the date for the following week, you also need to include the date for the previous week at the start and the following week at the end of the date range, with zero calls. I've coloured the formula cell blue in the attached file to illustrate this, where this formula is in C3 and copied across to T3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then, with a date of 1/10/2012 in D6, this formula in E6 gives the total for that month:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and I've copied both cells across with a gap of a few cells, and then changed the dates in the first of those cells (formatted to show the month name only).

    Hope this helps.

    Pete
    Attached Files Attached Files
    Last edited by Pete_UK; 08-23-2012 at 04:35 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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