Results 1 to 3 of 3

Thread: Need help to convert monthly data to Quarterly in Microsoft Excel

  1. #1
    Join Date
    Jan 2012
    Posts
    61

    Need help to convert monthly data to Quarterly in Microsoft Excel

    I would like to convert high frequency (such as monthly) time series data to lower frequency (such as quarterly) data.

  2. #2
    Join Date
    May 2011
    Posts
    448

    Re: Need help to convert monthly data to Quarterly in Microsoft Excel

    I am assuming that you have a column of dates (daily) that you also want to be able summarize for quarterly purposes in a Pivot Table or some other format. Probably the best way I have been able to come up with a solution in my experience is this. Create a helper row that gives you the month with the following formula (this assumes that you have the daily date in one of the columns of existing data): =text(A2,"mmmm"). If A1 is the cell at the top of the column that has the date in it, this formula will return a month name (i.e. September). If you are going to want to look at quarterly data over a period of several years, then you will also need a year column. Again a helper row will allow you do this with the following formula: =text(A2,"yyyy") This will return the year from the date cell.

    Now to my knowledge there is no formula for determining quarters, so I would sort the data by month, then assign the proper quarter # to the corresponding months. This will allow you to do a pivot table on the quarters and only see data for quarters. If this isn't what you are looking for let me know a few more specifics and I will see what I can do to help.

  3. #3
    Join Date
    May 2011
    Posts
    410

    Re: Need help to convert monthly data to Quarterly in Microsoft Excel

    One way is illustrated by the example in the CSV file below. If that doesn't suit your needs, more details about your requirements might help. Check the below illustration and try it.

    ---Quarter---
    • Jan,57,=INT((ROW()+1)/3)
    • Feb,232,=INT((ROW()+1)/3)
    • Mar,76,=INT((ROW()+1)/3)
    • Apr,90,=INT((ROW()+1)/3)
    • May,32,=INT((ROW()+1)/3)
    • Jun,6,=INT((ROW()+1)/3)
    • Jul,98,=INT((ROW()+1)/3)
    • Aug,34,=INT((ROW()+1)/3)
    • Sep,21,=INT((ROW()+1)/3)
    • Oct,56,=INT((ROW()+1)/3)
    • Nov,89,=INT((ROW()+1)/3)
    • Dec,90,=INT((ROW()+1)/3)

    ---Quarter,Sum,---
    • 1,"=SUMIF(C2:C13,A17,B2:B13)"
    • 2,"=SUMIF(C2:C13,A18,B2:B13)"
    • 3,"=SUMIF(C2:C13,A19,B2:B13)"
    • 4,"=SUMIF(C2:C13,A20,B2:B13)"

Similar Threads

  1. Replies: 4
    Last Post: 04-01-2014, 10:47 AM
  2. Replies: 3
    Last Post: 13-01-2012, 04:57 PM
  3. How to convert Excel file data into XML code
    By Arif15 in forum Software Development
    Replies: 3
    Last Post: 20-10-2009, 07:21 PM
  4. How to graph daily data in to monthly excel sheet
    By Sunny55 in forum Windows Software
    Replies: 2
    Last Post: 04-06-2009, 08:32 PM
  5. Convert tabular data to a matrix in Excel
    By Rookieinexcel in forum Windows Software
    Replies: 2
    Last Post: 23-04-2009, 10:19 PM

Tags for this Thread

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
  •  
Page generated in 1,716,003,270.53050 seconds with 17 queries