+ Reply to Thread
Results 1 to 4 of 4

Summing quarters based on monthly data

  1. #1
    Registered User
    Join Date
    08-30-2016
    Location
    Dover, DE
    MS-Off Ver
    2010
    Posts
    4

    Summing quarters based on monthly data

    I work for a non-profit that provides healthcare and we have to report monthly data on productivity. I have attached a very short version of a significantly larger spreadsheet.

    Currently, an end user will type in a reporting period on another sheet that P1 will reference. As you can see, the months then adjust to a preceding month to show historic data. The months range is dynamic based on user input. The end user also selects a facility or "All" and the data then gets pulled for each indicator through index/match based on the users input of date and facility. I have simplified it in the sample to just show the data. My current quarterly formula looks at blocks of three months at a time but those are not proper "quarters" (Jan-Mar, Apr-June, Jul-Sep, and Oct-Dec) as you can see entering 7/1/2017 should yield a Q3 '17 of 65, not 172 (sum of 5/1-7/1). I have been trying multiple variations of offset (not included) but cannot get it to calculate properly.
    It is not letting me attach a file. I will try in a subsequent post.

    I read through about 20 posts and couldn't find exactly what I was looking for.

    Any help is much appreciated.
    Last edited by jcon87; 03-04-2019 at 05:49 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,929

    Re: Summing quarters based on monthly data

    Do not create an additional post. The paperclip icon does not work. Click on the Go Advanced button in the quick reply window. Scroll down to manage attachments.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    08-30-2016
    Location
    Dover, DE
    MS-Off Ver
    2010
    Posts
    4

    Re: Summing quarters based on monthly data

    Attached file
    Attached Files Attached Files
    Last edited by jcon87; 03-04-2019 at 05:48 PM. Reason: Attachment

  4. #4
    Registered User
    Join Date
    08-30-2016
    Location
    Dover, DE
    MS-Off Ver
    2010
    Posts
    4

    Re: Summing quarters based on monthly data

    After a week of reading, searching, and trying, I finally have a solution for anyone else trying to do this:

    For Current Q:

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

    For previous Q:

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


    To go further back, just change the "-3" after each EDATE to a multiple of 3 (e.g.: -3 = Qx - 1Q, -6 = Qx - 2Q, -9 = Qx - 3Q).*

    Hopefully this will save someone else the time. Big thanks to Hangman and Daddylonglegs (used one of their conversations to solve my own problem).

+ 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. [SOLVED] Summing the monthly costs of a project
    By Bobski9776 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2014, 06:54 AM
  2. [SOLVED] Formula for Monthly Data into Quarters
    By bjnockle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-25-2014, 09:01 PM
  3. Replies: 1
    Last Post: 03-08-2013, 04:18 PM
  4. Replies: 5
    Last Post: 03-07-2013, 11:38 AM
  5. Converting Monthly figures to Quarters
    By Jake in forum Excel General
    Replies: 1
    Last Post: 08-03-2006, 10:45 PM
  6. Summing Monthly values
    By ATK in forum Excel General
    Replies: 1
    Last Post: 02-16-2006, 06:45 PM
  7. summing up daily data in a monthly compilation
    By mark_vi_ in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2005, 12:05 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