Is there an easy way to convert a column of monthly data into a column of quarterly data (average of months in the quarter)?
Is there an easy way to convert a column of monthly data into a column of quarterly data (average of months in the quarter)?
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.
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.
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.
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.
Bookmarks