Convert Monthly Data to Quarterly Data

shantanu97

New Member
Joined
Aug 27, 2020
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Need to convert "Data-Interest" from Monthly data to Quarterly Data. Attach File

Quarter is from July to June.

Quarter 1 - July,Aug,Sep
Quarter 2- Oct,Nov,Dec
Quarter 3-Jan, Feb, March
Quarter 4-April,May,June

Please help me, how to do this in excel?
 

Attachments

  • 1.JPG
    1.JPG
    95.2 KB · Views: 663

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
=VLOOKUP(MONTH(A2),IF({1,0},{7;8;9;10;11;12;1;2;3;4;5;6},{1;1;1;2;2;2;3;3;3;4;4;4}),2,0)

With Ctrl+Shift+Enter
 
Upvote 0
Hi May this Help

Book1
ABCDEFGHIJKLMNOPQRSTUVW
1
2May-928.379.125.478.118.819.65
3Jun-927.047.838.95.057.348.319.34
4Jul-926.227.058.34.76.457.458.61
5Aug-927.868.418.974.558.198.859.41
6Sep-927.918.328.944.588.078.779.36
7Oct-927.898.298.864.58.038.79.25
8Nov-928.018.489.144.478.218.969.56
9Dec-927.748.248.944.587.978.749.36
10Jan-937.4988.64.67.748.458.98
11Feb-936.677.267.984.556.957.648.3
12Mar-936.637.057.824.356.657.388.09
13Apr-936.276.697.554.316.297.027.78
14May-936.597.097.714.036.637.287.97
15Jun-936.226.837.374.036.296.947.69
16Jul-935.856.436.883.715.966.67.19
17Aug-935.666.166.643.175.816.336.91
18Sep-935.886.356.843.246.056.557.1
19Oct-935.516.016.493.355.76.226.76
20Nov-935.846.366.813.56.096.597.09
21Dec-935.876.176.683.65.956.426.94
22Jan-945.626.056.363.595.716.146.64
23
24
25Because OF First Month of this Quarter Miss reads 0
26000000008.379.125.478.118.819.657.047.838.95.057.348.319.34
276.227.058.34.76.457.458.617.868.418.974.558.198.859.417.918.328.944.588.078.779.36
287.898.298.864.58.038.79.258.018.489.144.478.218.969.567.748.248.944.587.978.749.36
297.4988.64.67.748.458.986.677.267.984.556.957.648.36.637.057.824.356.657.388.09
306.276.697.554.316.297.027.786.597.097.714.036.637.287.976.226.837.374.036.296.947.69
315.856.436.883.715.966.67.195.666.166.643.175.816.336.915.886.356.843.246.056.557.1
325.516.016.493.355.76.226.765.846.366.813.56.096.597.095.876.176.683.65.956.426.94
335.626.056.363.595.716.146.6400000000000000
Sheet2
Cell Formulas
RangeFormula
C26:W33C26=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))
 
Upvote 0
Here is another option to get the quarter, but as CA_Punit asked. What are you trying to do?

Book1
AB
1DateQuarter
21/1/19923
32/1/19923
43/1/19923
54/1/19924
65/1/19924
76/1/19924
87/1/19921
98/1/19921
109/1/19921
1110/1/19922
1211/1/19922
1312/1/19922
Sheet1
Cell Formulas
RangeFormula
B2:B13B2=CHOOSE(MONTH(A2),3,3,3,4,4,4,1,1,1,2,2,2)
 
Upvote 0
Based on Dossfm0q formula, with a little correction of he's formula (column number increased).
If the layout are what OP want, I don't know. Maybe he returns, and reply to some of the proposals ;)

Cell Formulas
RangeFormula
L2:S10L2=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:B2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
AF2:AK10,U2:AB10U2=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:J2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
AD2:AE10AD2=INDIRECT(ADDRESS((((ROW($B2)-1)*3)-ROW($B$2))+(CEILING.PRECISE(COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)/8),COLUMN($B2)+(8-CEILING.PRECISE(COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8),8)+COLUMNS($B2:R2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*8))-1))
K3:K10K3=EDATE(K2,3)
 
Upvote 0
Quarters in normal sequence

Month to Quarter.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1Month
2May-928.379.125.478.118.819.65
3Jun-927.047.838.95.057.348.319.34
4Jul-926.227.058.34.76.457.458.61
5Aug-927.868.418.974.558.198.859.41
6Sep-927.918.328.944.588.078.779.36
7Oct-927.898.298.864.58.038.79.25
8Nov-928.018.489.144.478.218.969.56
9Dec-927.748.248.944.587.978.749.36
10Jan-937.4988.64.67.748.458.98
11Feb-936.677.267.984.556.957.648.3
12Mar-936.637.057.824.356.657.388.09
13Apr-936.276.697.554.316.297.027.78
14May-936.597.097.714.036.637.287.97
15Jun-936.226.837.374.036.296.947.69
16Jul-935.856.436.883.715.966.67.19
17Aug-935.666.166.643.175.816.336.91
18Sep-935.886.356.843.246.056.557.1
19Oct-935.516.016.493.355.76.226.76
20Nov-935.846.366.813.56.096.597.09
21Dec-935.876.176.683.65.956.426.94
22Jan-945.626.056.363.595.716.146.64
23
24
25QuarterFirst Month of QuarterSecond Month of QuarterThird Month of Quarter
26Quarter 2000000008.379.125.478.118.819.657.047.838.95.057.348.319.34
27Quarter 36.227.058.34.76.457.458.617.868.418.974.558.198.859.417.918.328.944.588.078.779.36
28Quarter 47.898.298.864.58.038.79.258.018.489.144.478.218.969.567.748.248.944.587.978.749.36
29Quarter 17.4988.64.67.748.458.986.677.267.984.556.957.648.36.637.057.824.356.657.388.09
30Quarter 26.276.697.554.316.297.027.786.597.097.714.036.637.287.976.226.837.374.036.296.947.69
31Quarter 35.856.436.883.715.966.67.195.666.166.643.175.816.336.915.886.356.843.246.056.557.1
32Quarter 45.516.016.493.355.76.226.765.846.366.813.56.096.597.095.876.176.683.65.956.426.94
33Quarter 15.626.056.363.595.716.146.6400000000000000
Sheet2
Cell Formulas
RangeFormula
C26:W33C26=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))
A26:A33A26="Quarter "&CEILING(MONTH(INDIRECT(ADDRESS((((ROWS(A$2:A2)-1)*3)+ROW(A$2))-IF(AND(ROWS(A$2:A2)>1,MOD(MONTH(A$2)+2,3)+1>1),((MOD(MONTH(A$2)+2,3)+1)-1),0),COLUMN(A2)))),3)/3
 
Upvote 0
Quarters in normal sequence

Month to Quarter.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1Month
2May-928.379.125.478.118.819.65
3Jun-927.047.838.95.057.348.319.34
4Jul-926.227.058.34.76.457.458.61
5Aug-927.868.418.974.558.198.859.41
6Sep-927.918.328.944.588.078.779.36
7Oct-927.898.298.864.58.038.79.25
8Nov-928.018.489.144.478.218.969.56
9Dec-927.748.248.944.587.978.749.36
10Jan-937.4988.64.67.748.458.98
11Feb-936.677.267.984.556.957.648.3
12Mar-936.637.057.824.356.657.388.09
13Apr-936.276.697.554.316.297.027.78
14May-936.597.097.714.036.637.287.97
15Jun-936.226.837.374.036.296.947.69
16Jul-935.856.436.883.715.966.67.19
17Aug-935.666.166.643.175.816.336.91
18Sep-935.886.356.843.246.056.557.1
19Oct-935.516.016.493.355.76.226.76
20Nov-935.846.366.813.56.096.597.09
21Dec-935.876.176.683.65.956.426.94
22Jan-945.626.056.363.595.716.146.64
23
24
25QuarterFirst Month of QuarterSecond Month of QuarterThird Month of Quarter
26Quarter 2000000008.379.125.478.118.819.657.047.838.95.057.348.319.34
27Quarter 36.227.058.34.76.457.458.617.868.418.974.558.198.859.417.918.328.944.588.078.779.36
28Quarter 47.898.298.864.58.038.79.258.018.489.144.478.218.969.567.748.248.944.587.978.749.36
29Quarter 17.4988.64.67.748.458.986.677.267.984.556.957.648.36.637.057.824.356.657.388.09
30Quarter 26.276.697.554.316.297.027.786.597.097.714.036.637.287.976.226.837.374.036.296.947.69
31Quarter 35.856.436.883.715.966.67.195.666.166.643.175.816.336.915.886.356.843.246.056.557.1
32Quarter 45.516.016.493.355.76.226.765.846.366.813.56.096.597.095.876.176.683.65.956.426.94
33Quarter 15.626.056.363.595.716.146.6400000000000000
Sheet2
Cell Formulas
RangeFormula
C26:W33C26=INDIRECT(ADDRESS((((ROW($C2)-1)*3)-ROW($C$2))+(CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)/7),COLUMN($C2)+(7-CEILING(COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7),7)+COLUMNS($C2:C2)-(((MOD(MONTH($A$2)+2,3)+1)-1)*7))-1))
A26:A33A26="Quarter "&CEILING(MONTH(INDIRECT(ADDRESS((((ROWS(A$2:A2)-1)*3)+ROW(A$2))-IF(AND(ROWS(A$2:A2)>1,MOD(MONTH(A$2)+2,3)+1>1),((MOD(MONTH(A$2)+2,3)+1)-1),0),COLUMN(A2)))),3)/3

If I look in the raw data from the OP, i can see, Dossfm0q, that you still miss a column in each Quarter. That's why I did make a "corrected" version of your good way of setting it up. When you look closer to the years from 95 and forwards, all 8 columns are in use!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top