TechArena Community How to convert monthly data into quarterly data in Microsoft Excel

# How to convert monthly data into quarterly data in Microsoft Excel

## MS Office Support

#1
31-12-2011
 Member Join Date: Dec 2011 Posts: 4
How to convert monthly data into quarterly data in Microsoft Excel

Is there an easy way to convert a column of monthly data into a column of quarterly data (average of months in the quarter)?

#2
31-12-2011
 Member Join Date: Aug 2011 Posts: 459
Re: How to convert monthly data into quarterly data in Microsoft Excel

You can try to do this : =SUM(OFFSET(\$M\$3,ROW()*3-9,0,3,1)). Replace \$M\$3 with the cell address of the first month's data. Replace the 9 with 3* for the row number of that first month's data (my example was in row 3; 3*3=9). Autofill that formula down through three more rows.
#3
31-12-2011
 Member Join Date: Jul 2011 Posts: 432
Re: How to convert monthly data into quarterly data in Microsoft Excel

To group date fields, you can select the field on your ACT, then right-click context menu, then you select "Group", and you choose your month quarter year period or another in the window that appears. The first date must be an end of quarter. The formula shifts the previous date of 4 months and is placed on day 0 which is the last day of previous month.
#4
04-01-2014
 Member Join Date: Aug 2011 Posts: 561
Re: How to convert monthly data into quarterly data in Microsoft Excel

You have to understand the proper formula to get result. In a year there are around 3 quarters. This are divided in the gap of 4 months. So first you have to find out the month average which will be considered on the basis of 3 quarters. You have to begin with numbering each and every month. You can insert a column and start doing the same. Then you can try moving ahead to pull out a quarterly data from the same. You will need to use vlookup.
#5
04-01-2014
 Member Join Date: Aug 2011 Posts: 459
Re: How to convert monthly data into quarterly data in Microsoft Excel

This can be done by locating three months average first. We are going to take 3 quarters of a year under consideration. You can add a extra column and use it for getting three months average. A single cell in that new column will give you the average of 3 months. You have to decide the months here. Then you go ahead with using vlookup to get final output. This is a bit complicated but I found a video on the same on YouTube. You can try searching the same.